DB2 Export error - SQL10018N The disk is full

While trying to run this export command

db2 "export to /filepath/schema.tablename.ixf of ixf messages v.msgs select * from wcsproln.catentryattr”

I got this error "SQL10018N  The disk is full.  Processing was ended.”

df -g showed that /proddump had 19 GB free space.

Looked into the db2diag log and found this:

2016-05-17-18.51.46.775102-240 E48658514A815      LEVEL: Error (OS)

PID     : 48627918             TID  : 1           PROC : db2bp

INSTANCE: db2inst1             NODE : 000

APPID   : *LOCAL.db2inst1.160517215412

EDUID   : 1

FUNCTION: DB2 UDB, oper system services, sqlowrite, probe:60

MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."

          DIA8312C Disk was full.

CALLED  : OS, -, write

OSERR   : EFBIG (27) "A file cannot be larger than the value set by ulimit."

DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes

0x0FFFFFFFFFFFE170 : 0000 0004 0000 0080                        ........

DATA #2 : unsigned integer, 8 bytes

34

DATA #3 : signed integer, 8 bytes

-1

DATA #4 : String, 105 bytes

Search for ossError*Analysis probe point after this log entry for further

self-diagnosis of this problem.

I did some research on this error message "A file cannot be larger than the value set by ulimit.” and found that file size limit set may be too low.

Command “ulimit -a” showed the limits set:

time(seconds)        unlimited

file(blocks)         2097151

data(kbytes)         131072

stack(kbytes)        32768

memory(kbytes)       32768

coredump(blocks)     2097151

nofiles(descriptors) 2000

threads(per process) unlimited

processes(per user)  unlimited

There are two ways to change these values:

1. Edit the limits file under /etc/security/limits (takes effect after reboot) 
2. Use the chuser command to change individual user settings (logout and login required)

Used the chuser command to change the fsize value:

chuser fsize=5000000 db2inst1

Logged off and logged back in and tried the export command. Voila !! It worked.

Copy Data from DB2 for z/OS to DB2 for LUW (UDB)

One of the applications developer in our shop wanted to know if he can copy data from a mainframe DB2 table to DB2 UDB on LUW. Though there are several ways of doing it, this is the one suggested him assuming the table structure in z/OS and LUW are the same.

1. Some of the tools that can be used to achieve this: Quest Central for DB2, Toad for DB2 or DB2CMD interface

2. Connect to the mainframe database (CONNECT TO ssid) and issue the EXPORT command "EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name"
Note: Don't forget to check the message file before moving onto the next step

3. Connect to the LUW database and issue the IMPORT command
"IMPORT FROM d:\file_name.ixf OF IXF MESSAGES d:\msg_import.txt INSERT INTO prefix.table_name"
Note: Don't forget to check the message file

It's that simple. But remember that this works well for relatively smaller tables. For larger tables, you may have to use the LOAD command with proper COMMIT COUNT.

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.