Watch, Follow, &
Connect with Us

:: Database Nirvana :: Database, Cloud and many more!!

IBM Champion for DB2 (2008-Present)

IBM Champion web badge


Embarcadero® DBArtisan® 2016

Embarcadero® DBArtisan® 2016 is a revolutionary, team-enabled database administration solution using integrated performance analytics and team sharing capabilities to maximize availability, performance, security and collaboration across multiple DBMSs. DBArtisan is the essential DBA toolset to dramatically lower costs and save time by leveraging performance insights to identify issues and pinpoint resolutions.

DB Team Server (Included)

Work Smarter. Get Team-Enabled.

With DBA teams struggling to keep pace with the explosion of data, data sources and rapid development cycles, productivity solutions are becoming paramount. Being able to access information from team members across your organization save you time and keeps you ahead of the game. With DB Team Server, you’ll have a holistic view of the enterprise database landscape and the ability to streamline communications between teams.

Embarcadero® DB Team Server provides:

  • Manage and analyze corporate data assets
  • Monitoring and alert notifications
  • Improved responsiveness with insights into availability, performance and storage.
  • Easy access to highlighting utilization statistics and data volumes
  • Shared Password Locker
  • Share data source repositories

Check out a Demo below.

 

 

Contact us for more information at sales at embarcadero dot com

Posted by Anil Mahadev on November 4th, 2015 under Uncategorized | Comment now »


ER/Studio Model Repository Database Sizing Guide for IBM DB2

Howdy!

In this final installment, I will be covering on how to ensure optimum model repository performance for IBM DB2.

[Update Statistics]

RUNSTATS command

Updates statistics in the system catalog about the characteristics of a table and/or associated indexes, or statistical views. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data.

  1. Collect statistics on the table only, on all columns without distribution statistics: 
  2. Collect statistics on the table only, on columns empid and empname with distribution statistics: 
  3. Collect statistics on the table only, on all columns with distribution statistics using a specified number of frequency limit for the table while picking the num_quantilesfrom the configuration setting: 
  4. Collect statistics on the table only, on all columns with distribution statistics, and on column group JOB, WORKDEPT, and SEX:
  5. Collect statistics on a set of indexes: 
  6. Collect basic statistics on all indexes only: 
  7. Collect basic statistics on the table and all indexes using sampling for the detailed index statistics collection: 
  8. Collect statistics on table, with distribution statistics on columns empid, empname and empdept and the two indexes Xempid and Xempname. Distribution statistics limits are set individually for empdept, while the other two columns use a common default: 
  9. Collect statistics on all columns used in indexes and on all indexes:
  10. Collect statistics on all indexes and all columns without distribution except for one column. Consider T1 containing columns c1, c2, …., c8 
  11. Collect statistics on table T1 for the individual columns c1 and c5 as well as on the column groups (c2, c3) and (c2, c4). Multicolumn cardinality is very useful to the query optimizer when it estimates filter factors for predicates on columns in which the data is correlated. 
  12. Collect statistics on table T1 for the individual columns c1 and c2. For column c1 also collect the LIKE predicate statistics. 
  13. Register a statistics profile to collect statistics on the table only, on all columns with distribution statistics using a specified number of frequency limit for the table while picking the num_quantiles from the configuration setting. The command also updates the statistics as specified. 
  14. Register a statistics profile to collect statistics on the table only, on all columns with distribution statistics using a specified number of frequency limit for the table while picking the num_quantiles from the configuration setting. Statistics are not collected. 
  15. Modify the previously registered statistics profile by changing the NUM_FREQVALUES value from 50 to 30. The command also updates the statistics as specified. 
  16. Modify the previously registered statistics profile by changing the NUM_FREQVALUES value from 50 to 30. Statistics are not collected. 
  17. Modify the previously registered statistics profile by adding column  and column group (, ) options. The command also updates the statistics as specified. 
  18. Modify the previously registered statistics profile by adding column  and column group (, ) options. Statistics are not collected. 
  19. Collect statistics on a table using the options recorded in the statistics profile for that table: 
  20. Query the RUNSTATS command options corresponding to the previously registered statistics profile stored in the catalogs of the table: 
  21. Collect statistics, including distribution statistics, on 30 percent of the rows: 
  22. To control the sample set on which statistics will be collected and to be able to repeatedly use the same sample set, you can do so as follows:  Issuing the same statement as above will result in the same set of statistics as long as the data has not changed in the interim.
  23. Collect index statistics as well as table statistics on 1.5 percent of the data pages. Only table data pages and not index pages are sampled. In this example 1.5 percent of table data pages are used for the collection of table statistics, while for index statistics all the index pages will be used: 
  24. Collect statistics for a statistical view, on all columns, without distribution statistics:
  25. Collect statistics for a statistical view, with distribution statistics on the columns ,  and . Distribution statistics limits are set for the  column, while the other columns use a common default:
  26. Collect statistics, including distribution statistics, on 10 percent of the rows using row level sampling:
  27. Collect statistics, including distribution statistics, on 2.5 percent of the rows using data page level sampling. Additionally, specify the repeated use of the same sample set. For this command to succeed, the query must be such that the DB2 database system can successfully push data page sampling down to one or more tables. Otherwise, an error (SQL 20288N) is issued. 
  28. Register a statistics profile to collect statistics on the view and on all columns with distribution statistics as specified:
  29. Modify the previously registered statistics profile. This command also updates the statistics as specified:

 

[Source: IBM DB2 Documentation]

[Perform Table Re-Orgs and Index Re-Orgs]

Before you perform a Table Re-Org, make sure you run the REORGCHK command.

REORG TABLE commands and REORG INDEXES ALL commands can be issued on a data partitioned table to concurrently reorganize different data partitions or partitioned indexes on a partition. When concurrently reorganizing data partitions or the partitioned indexes on a partition, users can access the unaffected partitions but cannot access the affected partitions. All the following criteria must be met to issue REORG commands that operate concurrently on the same table:

  • Each REORG command must specify a different partition with the ON DATA PARTITION clause.
  • Each REORG command must use the ALLOW NO ACCESS mode to restrict access to the data partitions.
  • The partitioned table must have only partitioned indexes if issuing REORG TABLE commands. No non-partitioned indexes (except system-generated XML path indexes) can be defined on the table.

    [Source: IBM Knowledge base]

 

Again as always, ensure these are run during non-production hours and this will ensure a smooth running of your ER/Studio Model Repository IBM DB2 Database!

Stay tuned for more posts on our products!

 

Posted by Anil Mahadev on November 4th, 2015 under Uncategorized | Comment now »


ER/Studio Model Repository Database Sizing Guide for Oracle

Howdy! 

In a previous article, I showcased on how to implement table statistics and Index Re-orgs in SQL Server for the Model Repository.

In today’s blog post, I will be showing on how to update table statistics on the ERSRepo Schema for an Oracle database/schema.

[Tip 1]

It is highly recommended to create a schema for the ER/Studio Model Repository.

[Gather Statistics in Oracle]

exec dbms_stats.gather_schema_stats( -
ownname          => ‘SCOTT’, -
options          => ‘GATHER AUTO’, -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt       => ‘for all columns size repeat’, -
degree           => 15 -
)

[Table ReOrg]

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. 

 

Good Luck! In the next article, I will be showcasing how to perform Database optimization in IBM DB2.

Posted by Anil Mahadev on November 4th, 2015 under Uncategorized | Comment now »


ER/Studio Database Sizing Guide - Best Practices

ER/Studio Database Sizing Guide - Best Practices

Howdy!

In this blog post, let us shed some light on how to better leverage Capacity planning and Database Sizing for ER/Studio’s Repository Database.

I will be covering each individual Database Platform per blog post.

In today’s series, I will be covering Microsoft SQL Server:

For Microsoft SQL Server Database: 

Perform the following steps:

After approx a month or so of repository activity, 

1) Run sp_updatestats to update table statistics

2) During non-production hours perform a re-org on the indexes

Tomorrow I will cover Oracle topics.

Stay tuned!

Posted by Anil Mahadev on October 20th, 2015 under Uncategorized | Comment now »


Setting up ER/Studio Repository with Microsoft SQL Server in Amazon Web Services

Howdy!

In this series, we will explore  on how easy it is to setup our Model Repository in the Amazon Web Services Cloud Environment.

Now I will not be going through setting up an AWS EC2 instance etc. However, I will guide you through the steps you would need in order to successfully setup the Repository.

[Step 1 --> Choosing the Correct Virtual Machine from the Gallery]

My personal fav is the SQL Server 2008R2 with Windows 2008 R2.

You are free to choose amongst other popular ones.

Once you have chosen that, make sure you add the following ports to the Security Group as shown below.

One of the easiest ways to know which ports to open, Launch ER/Studio and under the Repository Menu –> Repository Properties –> Under the Server Tab, make a note of the ports as shown below

 

 

Now ensure the above reflect in your Endpoints of the VM as well.

And one final step is to make sure that within your VM, that you explicitly enable the above ports in order to communicate.

[Repository Setup]

Once logged into the Server, perform the Repository setup as you would on an on-premise environment.

Reboot the VM.

[Client Connectivity]

Now in the EC2 Dashboard, make sure you that have the Public Virtual IP address to enter in our Repository Connection Menu in ER/Studio.

Now you should be able to connect without any issues.

========================

[Common Troubleshooting steps]

1) Check the Firewall, Check the Firewall!! Check The Firewall!!

 I said that three times intentionally

2) Make sure the Repository Services are running

3) ER/Studio Client is not blocking anything from a client perspective

4) Make sure the Endpoints are also added in the Firewall Exceptions

=======================

Congrats!

This should give y’all an excellent guide to setup our Embarcadero ER/Studio Model Repository in the Amazon Web Services!

Posted by Anil Mahadev on September 17th, 2015 under Uncategorized | Comment now »


Setting up ER/Studio Repository with Microsoft SQL Server in Microsoft Azure Virtual Machines

Howdy!

In this series, we will explore  on how easy it is to setup our Model Repository in the Microsoft Azure Cloud.

Now I will not be going through setting up an Azure VM etc. However, I will guide you through the steps you would need in order to successfully setup the Repository.

[Step 1 --> Choosing the Correct Virtual Machine from the Gallery]

My personal fav is the SQL Server 2008R2 with Windows 2008 R2.

You are free to choose amongst other popular ones.

Once you have chosen that, make sure you add the following ports to the EndPoints as shown below on Page 3 of the Wizard.

One of the easiest ways to know which ports to open, Launch ER/Studio and under the Repository Menu –> Repository Properties –> Under the Server Tab, make a note of the ports as shown below

 

Now ensure the above reflect in your Endpoints of the VM as well.

And one final step is to make sure that within your VM, that you explicitly enable the above ports in order to communicate.

 

[Repository Setup]

Once logged into the Server, perform the Repository setup as you would on an on-premise environment.

Reboot the VM.

 

[Client Connectivity]

Now in the Azure Dashboard, make sure you that have the Public Virtual IP address to enter in our Repository Connection Menu in ER/Studio.

Now you should be able to connect without any issues.

========================

[Common Troubleshooting steps]

1) Check the Firewall, Check the Firewall!! I said that three times intentionally

2) Make sure the Repository Services are running

3) ER/Studio Client is not blocking anything from a client perspective

4) Make sure the Endpoints are also added in the Firewall Exceptions

=======================

Congrats!

This should give y’all an excellent guide to setup our Embarcadero ER/Studio Model Repository in the Microsoft Azure Cloud!

Posted by Anil Mahadev on September 15th, 2015 under Uncategorized | Comment now »


Setting up Oracle for Embarcadero ER/Studio - Part 2

Howdy! Welcome to the next installment of the series.

In today’s series, we will explore the options for Oracle Client Setup needed for ER/Studio. 

Install the clients needed for your environments.

Reboot after installation. 

[Verifying Environment Variables]

1) Navigate to the Environment Variables Path. 

For more information on how to do it, check this article.

http://www.computerhope.com/issues/ch000549.htm 

Navigate to the PATH variable section and make sure that the Oracle client is at the very front of the path as shown in the screenshot below.

Notice: the Path points to the Oracle 64 bit client and 32 bit clients respectively.

Now time to ensure that your Oracle client is configured for TNSNames.ora

[Oracle Net Configuration Assistant]

Click on Start –> All Programs –> Oracle_home_client1 –> Application and Configuration Tools –> Oracle Network configuration Assistant

Now follow the screens as shown below

Don’t worry about the error, click on the change login and enter the appropriate credentials.

Once you have entered the right credentials you should be able to connect and get a successful connection.

Next, make sure you enter a Service Name, I have used XE as it represents my Instance.

Click Next to proceed.

Choose No and click Finish to save the changes.

We shall now make sure by doing a TNSPing in the Command Prompt.

Launch a Command Prompt Window and enter the following command

tnsping XE –> Replace XE with your Servicename and you should get a OK message as shown below

Now let us launch ER/Studio Data Architect and Choose File –> New –> Reverse Engineer from a Database

Choose Oracle as the Native Connection

Enter your Service Name under Data Source –> XE is our case

Enter the credentials and choose the schema you need in the next screen, and you should be able successful in using ER/Studio with Oracle!

From the Schema pick the HR schema from the Ellipse.

 

Congratulations!! Y’all now have Oracle Client successfully configured for ER/Studio.

Posted by Anil Mahadev on September 3rd, 2015 under Uncategorized | Comment now »


Setting up Oracle for Embarcadero ER/Studio - Part 1

In this blog post, I will be covering how easy it is for one to setup Oracle, in order to successfully work with Embarcadero’s leading Enterprise Data Modeling Tool, ER/Studio.

[Pre-requisites]

  • Oracle Database 32 / 64 bit Server Edition
  • ER/Studio Data Architect 32 / bit 
  • DBMS Client versions for the necessary ER/Studio product installed (if on a separate machine)

You may download the above software from the following location

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 

You may choose the appropriate edition and architecture.

Make sure that you have an Oracle Technology Network account. If not, create one, it’s Free!!

To download ER/Studio, use the following link

http://www.embarcadero.com/downloads 

Choose ER/Studio Data Architect –> You will be presented with the 64 bit option as a default, the choice is unto you.

ER/Studio Data Architect 

Once you have installed the above products, in the next section, we shall explore on how to setup the Oracle Database to ensure the connectivity works.

And will also ensure Data Architect can reverse engineer from an Oracle Database.

So stay tuned for the next in the series! 

Posted by Anil Mahadev on September 2nd, 2015 under Uncategorized | Comment now »


Data is the new Bacon!!! And Data Modeling is even cooler!

 Yes, you read that right! Data is indeed The new Bacon!! We have been hearing about it all over the place.

  So Rich in Information and still if done right cooked to Perfection!

With Embarcadero’s leading Data Modeling and Management tools! We know our audience! and have been doing this for decades!

With the newest kid on the block of our enterprise offerings, 

check out ER/Studio Enterprise Team Edition.

ER/Studio Enterprise Team edition provides the fastest, easiest, and most collaborative way for data management professionals to build and maintain enterprise-scale data models and metadata repositories.

Built-in facilities automate routine modeling tasks so users can analyze and optimize database and data warehouse designs faster than ever. 

 


[YoutubeButton url='https://youtu.be/2X4RW9AEoT4']
 
 
Check out the video and touch base for a personalized one-to-one Demo on ER/Studio Enterprise Team Edition!
 
Contact us at sales at embarcadero dot com
 
 

 

Posted by Anil Mahadev on August 20th, 2015 under Uncategorized | Comment now »


ER/Studio Enterprise Team edition - Future of Data Modeling is here!

ER/Studio Enterprise Team edition provides the fastest, easiest, and most collaborative way for data management professionals to build and maintain enterprise-scale data models and metadata repositories. Built-in facilities automate routine modeling tasks so users can analyze and optimize database and data warehouse designs faster than ever.

Model change management provides the ability to create and track tasks associated with data models in order to enable data architects to ensure their models stay aligned with database and application development efforts using an Agile workflow.

With a repository-based model management system and Team Server web interface, ER/Studio Enterprise Team edition provides the most productive way to share, document, and publish your models and metadata to distributed teams.

For more information check out: http://www.embarcadero.com/products/er-studio 

For more information please feel free to contact us : 
sales at embarcadero dot com

Posted by Anil Mahadev on August 17th, 2015 under Uncategorized | Comment now »




Server Response from: BLOGS1