Saturday, December 16, 2006

Strange: Group privileges are ignored for CREATE VIEW

SQL0551N does not have the privilege to perform operation "SELECT" on object

If a user receives this error while creating views, make sure the user is granted SELECT privilege individually. Group privileges are not considered for any table or view specified in the CREATE VIEW statement.

No, its not a bug. Its documented :)

Thursday, November 03, 2005

Cross-node recovery - TSM

To recover/access the backup image of the database that was backed up from a different server. Typically to access production database from dev/qa

Comment out "PASSWORDACCESS = generate" in file "/usr/tivoli/tsm/client/ba/bin/dsm.sys" by adding a '*' in the beginning of the line.

Now we should be able to query any backup image, provided you have the required details.

db2adutl query db <dbname> nodename <nodename> owner <owner> password <passwd>


Update the following db parameters in the target db with source db values from dsm.sys to restore the db backed-up from a different server
  • TSM_NODENAME
  • TSM_OWNER
  • TSM_PASSWORD
Now restore the database with 'use adsm' on the target server.

NOTE:
  • Revert back the dsm.sys & db params after the refresh. Else local backups to TSM will fail.
Other References: IBM Documentation

Tuesday, October 25, 2005

Parallel export for partitioned tables

db2_all "<<+$nodenum<db2 connect to $dbname; db2 export to $filename of del select * from $tablename where nodenumber($partitioningkey)=$nodenumber"

Example:

db2_all "<<+11<db2 connect to testdb; db2 export to /backup/test_table.del.11 of del select * from test_table where nodenumber(ID) = 11; db2 terminate"

NOTE:
  • Useful for large tables.
  • Be sure to export all the partitions in which the table is defined.
  • Export each partition to seperate filesystem for better performance.

Friday, October 14, 2005

Cataloging AS/400 database on DB2 Connect

Command sequence to catalog AS/400 database in DB2 Connect:

  • db2 catalog tcpip node <node_name> remote <host_name> server <port_no>
  • db2 catalog dcs database <local_name> as <real_db_name>
  • db2 catalog database <local_name> as <alias> at node <node_name> authentication dcs

Command sequence to catalog the as/400 database that was already cataloged in DB2 Connect.

  • db2 catalog tcpip node <node_name> remote <host_name_or_address> server <port_no>*
  • db2 catalog database <local_name> as <alias> at node <node_name>

*Use the portnumber of DB2 connect not AS/400

Thursday, August 04, 2005

SQL0031C File sqllib/bnd/db2clish.bnd could not be opened and SQL0805N


Received the above while accessing DB2 database from MS Access. db2clish.bnd was not found in the local machine.

Binding the files didn't help

o "DB2 bind @db2ubind.lst blocking all grant public" for the
DB2 utilities.

o "DB2 bind @db2cli.lst blocking all grant public" for CLI.

Re-installing the DB2 client fixed the problem.