Talk about DB2

Strange: Group privileges are ignored for CREATE VIEW

2006-12-16T15:06:00.000-08:00

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 :)

Maddocks Systems Success Story - DB2 Express Video

2006-12-09T00:53:00.000-08:00

Cross-node recovery - TSM

2005-11-03T15:23:00.000-08:00

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

Fly back in time and recover backups! :-)

2005-10-28T11:41:00.000-07:00

Parallel export for partitioned tables

2005-10-25T16:30:00.000-07:00

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.

Cataloging AS/400 database on DB2 Connect

2005-10-14T17:10:00.000-07:00

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

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

2005-08-04T17:23:00.000-07:00

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.

DB2 Stats!

2005-07-26T22:33:00.000-07:00

Instance Uptime

db2 get snapshot for dbm | grep -i start

Last Backup Timestamp

db2 get snapshot for db on dbname | grep backup

Database Activation timestamp

db2 get snapshot for db on dbname | grep First

SPM0438 The Sync point manager recovery log is bad

2005-05-05T16:04:00.000-07:00

You might also see the following message when starting DB2

SQL5043N Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.

or
with the following message and db2 will not start.

SQL5042N One of the communication protocol server support processes failed to start up.

Solution:

Stop the instance
Delete/move all the spm logs located in sqllib/spmlog/SPMLOGSD
Also del/move SPMLOG.LCF located in sqllib/spmlog
start the instance. Should starup without any error messages.

db2licm

2005-04-06T10:02:00.000-07:00

SQL8000N DB2START processing failed.

Cause: License Expired or License key have not yet been added.

If you get this after installing You might have to add the license key

As root

/usr/opt/db2_08_01/adm/db2licm -a /ese.sbcs/db2/license/db2ese.lic

License added successfully!!

SQL8017W The number of processors on this machine exceeds the defined
entitlement of...

db2licm -n "Product identifier" "Number of processors entitled to this product"

db2licm -n DB2ESE 2

Updates the number of processors to 2 for the product DB2ESE.

db2licm -l gives more information about the installed products with license info.

SQL0443N Routine "*RCH_1K16" (specific name "") has returned an error SQLSTATE with diagnostic text "CTE0101 A search engine operation failed

2005-03-09T00:06:00.000-08:00

Received this error while trying to do db2text (NetSearchExtender) operation on a database that was restored from a different machine.

Noticed the text index was not there in the restored databases. Recreating & Updating the Text index helped.

SQL10004C An I/O error occurred while accessing the database directory

2005-02-17T23:55:00.000-08:00

If there were no changes made to the Hardware. Then its most likely that the database directory file sqldbdir is corrupted.

You will not be able to create new databases, connect to or drop existing databases.

Solution:

Move all the files found in “sqllib/sqldbdir” to a different location.
Now catalog all the databases. For Local databases use: db2 catalog db “dbname”.
This should get ride of the error.

Causes:

I received this error after *successful* installation of DB2 8.1 fixpak 7a on DB2 8.1 Fixpak 2 OS AIX 5.3.

Include logs in online backup (As of DB2 8.2)

2005-02-14T23:51:00.000-08:00

db2 BACKUP DB “dbname” ONLINE TO “backupdir” INCLUDE LOGS

Above command includes the logs required to restore the database and bring back the database to a consistent point.

And to restore

db2 RESTORE DB “dbname” FROM “backupdir” LOGTARGET “logdir”

ROLLFORWARD DB “dbname” TO END OF LOGS AND STOP OVERFLOW LOG PATH “logdir”

To restore just the logs from the backup image

db2 restore db “dbname” logs from “backupdir” logtarget ‘logdir”

Setting up Tomcat for DB2 and some tips

2004-11-16T09:14:00.000-08:00

NOTE:

These were tested on win2k
"C:\jakarta-slide-2.0-tomcat-5.0.19\" is mentioned as $CATALINA_HOME

Pre-Requisites:

DB2.
Latest Java SDK

Steps:

Set an Enviromental Variable "JAVA_HOME" to the Java SDK's Home Directory.
Download & Install the latest binaries from Apache's Site (Get *.exe for windows to install as a service)
Copy "C:\Program Files\IBM\SQLLIB\java\db2java.zip" file to C:\jakarta-slide-2.0-tomcat-5.0.19\common\lib\" and rename the file to db2java.jar.

Running Tomcat as Service (Windows)

If you have installed *.exe file you already have Tomcat running as a service.
An work around for this is to run the startup.bat as a schedule task on system startup. This doesn't shows the Tomcat's command window (This method doesn't really install Tomcat as service)

Changing Tomcat's 8080 Port:

By Default Tomcat listens in the 8080 port (localhost:8080). It can be changed by changing values in the

$catalina_home\conf\server.xml

Change the value of Connector port to desired port.

THE PART THAT NEEDS TO BE CHANGED IN SERVER.XML

Connector port="8080"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
debug="0" connectionTimeout="20000"
disableUploadTimeout="true"

Restart Tomcat to see the changes.

Enabling Tomcat for SSI (Server Side Includes)

Rename $CATALINA_HOME/server/lib/servlets-ssi.renametojar to $CATALINA_HOME/server/lib/servlets-ssi.jar.

Remove the XML comments from around the SSI servlet and servlet-mapping configuration in $CATALINA_BASE/conf/web.xml.

DB2 Catalog Commands

Tomcat may have to be restarted if you re-catalog an existing database that is being used in your code.

Errors

Looking at the Tomcat log files will help you solve the error messages that you see on browser.

Location of log files $Catalina_home\logs

Resources:

http://jakarta.apache.org/tomcat/

PreCompiling and Running Embeded SQL

2004-11-15T13:24:00.000-08:00

Write your Embeded SQL C code with a file extension .sqc

Precompiling

db2 connect to database name
db2 prep filename.sqc
db2 connect reset

The Above Command on Sucess will give a filename.c file.

The C File needs to compilied with special flags as follows

gcc filename.c -Wall -I/home/db2inst1/sqllib/include/ -L/home/db2inst1/sqllib/lib -ldb2 -Wl,-rpath,/opt/IBM/db2/V8.1/lib -o

Run the outputfile to see the results.

NOTE:

The flags may vary if you have a username other than db2inst1.
And the flags need to be properly spaced.

Net Search Extender

2004-10-04T21:05:00.000-07:00

Intro:

DB2 Net Search Extender Version 8.1 is one of a family of DB2 Extenders™. It replaces DB2 Text Information Extender Version 7.2, and Net Search Extender Version 7.2 and offers users and application programmers a fast, versatile, and intelligent method of searching full-text documents stored in DB2, other databases, and file systems using SQL queries.

Basically, DB2 Net Search Extender searches text documents held in the column of a database table. The documents can be in various formats, such as HTML or XML. Rather than sequentially searching through the text documents that would take a considerable amount of time, Net Search Extender creates a text index in order to make documents searchable. A text index consists of significant terms extracted from the text documents.

Installation:

Make sure you have the appropriate DB2 Fixpack that matches Extender’s Pre-requisite. (RTFM)
Install NSE and Fixpack if its needs any. (You can get a trial download that can be used for 90days from IBM’s site.)
After installation run the sample script available in the “SQLLIB/samples/nsesample.bat”

nsesample.bat

The script creates the given DBNAME if it doesn’t exist and tests if the NSE installation was successful.

Check the nsesample.log file to make sure everything is fine.

Enabling a database for text search:

db2text start (Starts the NSE instance services)

db2text ENABLE DATABASE FOR TEXT CONNECT TO
(Prepares the database for use with NSE)

As DB2 commands db2text ? gives the syntax of the command.

Creating an Index:

Note: The table being referenced is the one which has the Text/HTML Document.

NSE needs an index to be created on the column that needs to be searched (You need to have a primary key to create an index). Create an index on the table that has the Text/HTML file that needs to be searched.

db2text create index db2ext. for text on

() UPDATE FREQUENCY D (*) H (*) M (10) connect to

(This command creates an index and sets the update frequency to 10 minutes, this is required to avoid synchronization issues)

Right after creation, update the index to fill the created index with data

db2text update index db2ext. for text connect to

Queries that can be used now:

SELECT AUTHOR,TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, ’"book"’) = 1

Searches for the word “book” in the text fields of the column comment.

SELECT AUTHOR,TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, ’"book" & “db2”’)=1

As you do in Google you can use logical operators like “&” “|” “NOT”. And they can be mixed in a search string.

SELECT AUTHOR, TITLE, NUMBEROFMATCHES(COMMENT, ’"book"’) FROM DB2EXT.TEXTTAB

Gives the number of matches found for the given word.

WITH TEMPTABLE(docid,score) AS (SELECT docid, SCORE(COMMENT,’"book"’) FROM DB2EXT.TEXTTAB)
SELECT * FROM TEMPTABLE WHERE score > 0 ORDER BY score ASC

Score tells you how well the documents meets your search. This can be used to order the search results.

This is an Interesting one!

SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, 'fuzzy form of 80 "Joh"') =1

Above query results will contain JOHN. This can be used when a normal search didn’t return any result. Ever wondered how Google is making up its “Did you mean” :)

Errors After A Redirected Restore!

2004-09-18T23:05:00.000-07:00

ADM1814E The current log path
"/ PATH/NODE0000/" is invalid.

ADM1811E DB2 will now switch to the default log path
"/PATH/NODE0000/SQL00004/SQLOGDIR/".

Whether you are restoring to a different machine or on the same machine never forget to change the NEWLOGPATH Database Parameter. If you don’t DB2 will switch to default log path. This will not be an issue if you are using default log path.

DB2 was unable to confirm logs were archived.
Return code 118489304, FirstArchNum 31953, HeadExtentID 31958

An error was received from the user exit program.
0x2FF21508 : 0x0000001C ....

Userexit program maintains a log which is more informative than the above and it will be in the files named USEREXIT.ERR & ARCHIVE.LOG, you should be able to find that in the LOGPATH.

You have to act based on that.

In my case I have to create a directory for the new database so that it can archive the logs.

This message keeps coming up sometimes after a redirected restore and not sure what it means… Help me!

SQL procedure executables recovery has been initiated. DBNAME
0x2FF1E330 : 0x4341535342202020

SQL procedure executables recovery ended. RC:
0x2FF1E310 : 0x00000000

Migrating from AIX to Windows

2004-09-15T23:40:00.000-07:00

There are the steps that i followed to migrate...

Haven't done the move on Prod DB. Will update with changes if any.

STEPS:

DB2 doesn’t support cross platform back-up and restore between AIX & Windows. Thus redirect restore cannot be used.

But DB2 UDB has tools to achieve this task.

db2move - Used for moving User tables (DATA)
db2look - Creates DDL to create the database objects in the new environment.
db2cfexp – Exports connectivity configuration to an export profile
db2cfimp – Import connectivity configuration information.

Steps for Moving from AIX to Windows

On AIX:

Use db2move to export all table data to PC/IXF files
Use db2look to generate DDL for all the database objects.
Use db2cfexp to export the configuration information to a file.
Transfer PC/IXF files & Configuration profile in Binary Mode.
Transfer db2look.sql & db2move.lst in ASCII Mode.

On Windows:

Make sure the version of DB2 is same with the appropriate Fixpack.
Create an instance with the same name as it was in AIX. (optional)
Create all the databases that you are moving.
Set current schema name.
Create required Buffer pools and Table Spaces for the DB.
Run the script generated by db2look
Import the configuration information using db2cfimp tool.
Run db2move in load mode. (Takes a while for large DB’s)
Update DB CFG to reflect the source DB.
Do a Full DB/Table space Backup to take the table space out of backup pending state and check for the integrity of the Backup.
Detect the tables that are in check pending state and bring it to normal state. (set integrity)
Check other db objects to make sure they are in Normal State.
All the configuration parameters need to be tuned according to the current hardware.

NOTE:

Don’t forget that default values for some parameters are different for windows & AIX

comments, suggestions, questions are welcome..

Welcome

2004-09-15T22:26:00.000-07:00

Hi all

DB2 DBA's out there

Come Share your thought's findings...