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

 

Maximum connections allowed on a DB2 database - MAXAPPLS and MAX_COORDAGENTS

Question: How can I find out the maximum amount of connections  allowed on a DB2 database?

Answer:  Work through this example to analyse the connections. The example indicates  support for 68 (with room to grow)  and the high watermark is 38.

 To check at a database level check MAXAPPLS . This parameter specifies the maximum number of concurrent applications that can be connected  to a database.

 

db2 get db cfg for DB_NAME | grep 'MAXAPPLS'
--returns
Max number of active applications            (MAXAPPLS) = AUTOMATIC(68)

 

To check at an instance level check :  MAX_COORDAGENTS , which is  used to limit the number of coordinating agents  and MAX_CONNECTIONS which reports the maximum number of client connections allowed per database partition

 

db2 get dbm cfg | grep 'MAX'
--returns
Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

To check for the high watermark use

db2 get snapshot for dbm | grep 'High'

--returns
High water mark for agents registered          = 38
High water mark for coordinating agents        = 38
       High water mark (bytes)                 = 15466496
       High water mark (bytes)                 = 851968
       High water mark (bytes)                 = 327680

 

Read More

Command to see connection on a DB2 database - list applications

DB2 Instance Memory and dbptnmem - DBA DB2

 

Db2 reduce tablespace with ALTER TABLESPACE REDUCE

There was some unexpected growth on a DB2 database server  during the business day.  Various jobs begin to fail due to disk issues . There was an immediate requirement to release  space to back to OS.

Sometimes unexpected growth occurs , regardless of scheduled reorganizations  and capacity planning.

The ALTER TABLESPACE REDUCE is useful in these circumstances.

Depending on whether the tablespace is set at automatic or non-automatic auto storage there is a different approach.

For non-automatic storage table spaces, specifies that existing containers are to be reduced in size

For automatic storage table spaces, specifies that the current high water mark is to be reduced, if possible, and that the size of the table space is to be reduced to the new high water mark

The ALTER TABLESPACE REDUCE syntax is :

 

db2 connect to mydb
db2 ALTER TABLESPACE USERSPACE1 REDUCE
db2 terminate

 

You may see the error message

 SQL1348W – The table space could not be reduced

 

In that case apply a reorg table which may allow below the watermark space release

 

Then proceed with ALTER TABLESPACE REDUCE

Other considerations

1)       Ensure the server is provisioned correctly for : disk space

2)       Regular backups are taken

3)       Sufficient window for reorganization

 Read More

DMS Container Space Full - DBA DB2

DB2 USERSPACE1 is full - DBA DB2

SQL0286N A default table space could not be found - DBA DB2

How to monitor a DB2 REORG

Question: How do I monitor a DB2 REORG , such as the following statement?

 reorg table SYSIBM.SYSCOLGROUPS inplace allow write access start

Answer:  Use the  db2pd   REORGS option. An example is :

         db2pd -d MYDB –reorgs

 

The resultset returns eleven columns. The “status” column –   is useful. It is possible to pause, stop, resume the REORG.   Be careful with STOP , as you cannot resume the REORG and will have to restart.

Some example commands are :

db2 reorg table     SYSIBM.SYSCOLGROUPS    inplace pause
db2 reorg table SYSIBM.SYSCOLGROUPS  inplace resume
db2 reorg table SYSIBM.SYSCOLGROUPS  inplace stop

 Read More

Reorg table DB2

DB2 table reorg and table truncate

DB2 Used Space and REORG