sqloopen -2079391743 sqlcode -970

I bumped into this error message (sqloopen -2079391743) when I tried to run "EXPORT" utility on a DB2 UDB table on my AIX box. No SQLCODE displayed on the screen. I couldn't find much information on the net, however, when I learnt about the DB2DIAG command, it made life easy. I issued the following command:

db2diag -rc -2079391743

and I got exactly what I was looking for:

Input ZRC string '-2079391743' parsed as 0x840F0001 (-2079391743).

ZRC value to map: 0x840F0001 (-2079391743)

V7 Equivalent ZRC value: 0xFFFFC601 (-14847)

ZRC class : Non-Critical Media Error (Class Index: 4)

Component: SQLO ; oper system services (Component Index: 15)

Reason Code: 1 (0x0001)

Identifer: SQLO_ACCD
Identifer (without component): SQLZ_RC_ACCD

Description: Access Denied

Associated information: Sqlcode -970
SQL0970N The system attempted to write to a read-only file.
Number of sqlca tokens : 0
Diaglog message number: 8701

I changed the datapath in my EXPORT command to a folder in which I had write access and the command ran fine.

"DB2LOOK" to extract DDL from DB2 for z/OS

Many of you may be aware that "db2look" command can be used to extract DDL from DB2 UDB for LUW database. But do you know that you can use this command to extract DDL from a DB2 database for z/OS? Here is how you do it.

1. Bring up a db2cmd prompt (press Start button on your PC, click Run, type db2cmd and hit enter)
2. Connect to your mainframe DB2 subsystem using the command "db2 connectto SSID user USERID". It'll prompt for the password. Enter the password and hit enter
3. Issue the following command:
db2look -d SSID -i USERID -w PWD -e -a -l -x -p -o FILENAME.TXT

The options specified extracts the following info:

-e ==> Extract DDL statements for database objects. DDL for the followingdatabase objects are extracted when using the -e option: Tables, Views, Automatic summary tables (AST), Aliases, Indexes, Triggers, Sequences, User-defined distinct types, Primary key, referential integrity, and check constraints, User-defined structured types, User-defined functions, User-defined methods, User-defined transforms, Wrappers, Servers, User mappings, Nicknames, Type mappings, Function templates, Function mappings, Index specifications, Stored procedures

-a ==> When this option is specified the output is not limited to the objects created under a particular creator ID. All objects created by all users are considered. For example, if this option is specified with the -e option, DDL statements are extracted for all objects in the database. If this option is specified with the -m option, UPDATE statistics statements are extracted for all user created tables and indexes in the database.
Note: If neither -u nor -a is specified, the environment variable USER isused.

-l ==> If this option is specified, then the db2look utility will generate DDL for user defined table spaces, database partition groups and bufferpools.

-x ==> If this option is specified, the db2look utility will generate authorization DDL (GRANT statement, for example).

More info is available in the DB2 V8 LUW Command Reference: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsptopic=/com.ibm.db2.udb.doc/core/r0002051.htm