Faster DB2 on Twitter

Follow me on Twitter for  DBA,SQL Server,DB2 and Performance Tuning very large databases infomation.

@jackvamvas

Email +Jack Vamvas at jack@sqlserver-dba.com  for any other questions. I do try and respond as quickly as possible. These posts may help you with urgent issues, also use the Search feature for other posts.

DB2 list schemas of a database - DBA DB2

Force connections of a database in DB2

DB2 Query Tuning – db2expln

How to list UDF in DB2

UDF in DB2 . How can I view  details on user defined functions?

To view user defined functions - UDF in DB2–  use the SYSCAT.ROUTINES catalog view to return a full list of user defined functions including – scalar function , table function, sourced function, method and procedure.

The example query returns Functions (routinetype = ‘F’ ) and  User Defined\SQL Bodied (origin in ('U', 'Q')

 

select r.routinename as FunctionName, r.text as FunctionBody from syscat.routines r where r.routinetype = 'F' and r.origin in ('U', 'Q')

 

Read More

DB2 list schemas of a database

DB2 SET CURRENT SCHEMA - DBA DB2

ADMIN_DROP_SCHEMA to drop a schema - DBA DB2

how to grant select privileges on all tables for DB2

List DB2 table columns and unique constraints

How can I list the DB2 table columns and unique constraints?

 To list DB2 table columns and constraints  use one of these methods – using syscat.columns , describe or db2look

--Method 1 – query syscat.columns
select colname from syscat.columns where tabname='ADDRESS'

--Method 2 – use the describe command
describe table dbo.address

--Method 3 –use db2look 
db2look  -e –d dbname –t tablename

 

Read More

DB2 - Collecting data for database performance problems - DBA DB2

DB2 – COPY a table - DBA DB2

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis - DBA DB2

 

Backup Retention Policy

Creating a backup and restore policy based on service level agreements, Recovery Point Objectives (RPO)  and Recovery Time Objectives (RTO) is a critical part of DBA work. Another long-term but important Backup and Restore component is devising a Backup Retention Policy.

 A Backup Retention Policy is defined as a set of rules outlining the steps of how long to retain a backup and the process of purging.

 The broad categories influencing the Backup Retention Policy are based on : Legal\Compliance, Recovery Points and Cost.

 As a first step identify a list of use cases within the organization. It is important to step back from the daily grind and look back to all the situations occurring in the last couple of years.

Some typical situations to consider are:

1)  How to deal with backup and restore  copy requirements in a project development  life cycle longer than the immediate DR period, such as 2 years.

2) Managing\Differentiating retention policies of , for example, financial data , which may have legal implications versus session management servers

3) The  current schedule backups the whole instance on the assumption the retention policy is based on per instance. Is  data classification policy  based on a per instance or per database? This impacts how database servers are built and maintained.

4) Retention of auxillary software , for example , recovering a db from 5 years ago – may require recovery of OS, fixpacks, passwords etc . Is the retention policy organised in a way to support a restore across a number of  code and information sources

5) Purging of expired backups.  Should they be automatic or manual?

Read More

DB2 – How to manage backup failures - DBA DB2

The Backup failed - DBA DB2

Data Center Migration , Database Servers and data migration planning

SQL Parser

Question : In my application, I am receiving SQL from other application. I want to validate that SQL text on my client. I don’t want to add the performance overhead to the database , what options are available?

Answer:  Parsing SQL code is an important part of code development . Parsing is the process of  analysing a string of symbols ,  based on the syntax rules.  There a number of options available and  will depend on your circumstances.

 

Option 1 – Make a DB2 call and allow the database to validate the SQL code. If you don’t want the overhead , then restore a copy of the database onto a lower environment

 

 

Option 2 – Download a SQL Parser

 

General SQL Parser  - Loads of features , but the documentation is poor. It’s a commercial tool but there is a free download

 

TSql100Parser Class in .net4 – Useful for SQL Server.Read More

 

JSQLParser – Creates Java classes hierarchy. Download

 

Hibernate – Not a parser but uses ANTLR for sql parsing

 

 

Read More

 

Trace sql statements in DB2 database  

DB2 Query Tuning – db2expln

 

SQL1042C An unexpected system error occurred. SQLSTATE=58004

An application owner reported an error on a DB2 database connection attempt.  A query on db2diag.log returned  this message

 

ADM1710C  The database is not accessible because the DB2 database 
          manager cannot write to the log control file "SQLOGCTL.LFH.2" on 
          database partition "0".  Possible reasons for this include: the file 
          does not exist; the file is not accessible to the database manager 
          because of network, file system, or OS problems; or incorrect file 
          permissions are preventing the database manager from writing to the 
          file.  Ensure that the file is accessible to the database manager and 
          that the file system is functioning properly, and restart or 
          reconnect to the database.

 

I attempted a DB2 database database connection, with a message of:

 

SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

 

The database was marked as bad .  the DB2 error messages correlated with Linux messages, relating to SCSI errors .

 

 
Dec 19 08:02:05 server1 kernel: Buffer I/O error on device dm-18, logical block 1562696
Dec 19 08:02:05 server1 kernel: lost page write due to I/O error on dm-18
Dec 19 08:02:05 server1 kernel: SCSI error : <0 0 2 0> return code = 0x20000
Dec 19 08:02:05 server1 kernel: end_request: I/O error, dev sdc, sector 12503752

 

DB2 couldn’t write  due to the Linux file system drive moving into a read-only state ,which it does under certain circumstances to protect itself . Using Commands check for read only Linux file system  , to verify the filesystem was in read only , I contact the Linux administrator.

The filesytem was remounted and placed back into the read/write state. Various DB2 files were recovered and made accessible again – which allowed the database to become available .

 Read More

Commands to check for read only Linux file system - DBA DB2

DB2 and Linux IO testing tools - DBA DB2

Command to see connection on a DB2 database - list applications