DB2 restore over existing database

Requirement to DB2 restore a copy of a DB2 database backup taken from another DB2 instance and replace the existing one Steps: 1)Take a full backup of the database of INSTANCE 1 db2 force applications all db2 backup database MYDB ##Transfer the backup copy to a relevant path db2 force applications all db2 restore db MYDB from /mypath/backups TAKEN AT 20110128111333 INTO MYDB REPLACE EXISTING Read more →

Synchronous statistics collections

An interesting feature of the RUNSTATS facility – as well as collecting statistics asynchronously (in the background) , it can also set a synchronous collection – at statement compilation time If the real time statistics is enabled – the optimizer makes the decision. Real statistics collection doesn’t preclude the background collection – which still happens in the background. The decision to enable real time collection should be based on application requirements. For example , you probably wouldn’t turn on real time statistics , if there are rarely any UPDATE,DELETE,INSERT statements A number of measures are in place to throttle the... Read more →

DB2 mysql export as output

I had to export some data from a Mysql database , for the purpose of creating a spreadsheet , which would be used for presentation purposes and also to import into a DB2 database. Initially I considered using an ODBC connection from my laptop , I do not have ODBC drivers for mysql on my laptop. The policy of the company is to not allow new drivers installed with centralised authority . Even without the drivers it was straightforward. Logged into mysql and created a resultset via the following command : Mysql>select p.page_title from page p inner join text t... Read more →

DB2 extend database server capacity

A quick solution for extending capacity on a database server is normally a)add more memory b)add disk space and expand containers. C)additional processors d)add partitions If adding more procesors and system is SMP the processors will automatically share storage resources and memory. Consider any changes that may need to occur to the following parameters: Default degree (dft_degree) Default degree configuration parameter Default value =1 and is equivalent to no intra-partition parallelism. If -1 , the optimizer decides n level of intra-partition parallelism Maximum degree of parallelism (max_querydegree) (Maximum degree of intra-partition parallelism )If -1 , the optimizer decides otherwise set... Read more →

DB2 enforcing business rules on data

When designing data and related business rules, there are a number of concepts available to the DBA . Consider the following in Primary Key definition Foreign key definitions User-defined distinct data types. Nulls-allowed columns Not null columns. Column value constraint rules. Before and after triggers. – (UPDATE,INSERT,DELETE) Generated row change timestamps. Distinct Data Unique indexes. Non-unique indexes In summary , these concepts serve a purpose – and can be used effectively to maintain business data. This must be weighed up against performance and maintenance considerations Ref:Jack Vamvas(http://www.dba-db2.com) Read more →