DB2 Dataload from PC to mainframe issue

[tdanielxyz]

Hi,
I have a situation where I need to load data from a note pad to a DB2 table in mainframe. I uploaded the data from PC to mainframe in 79 byte records. But, when I see the coma seperated data in mainframe, the column value is spread in two lines so Db2 can't read data properly.

For instance,
Below is the data in on PC side. Though it apperas here on two lines, it is on a single line in note pad.

Insert into A5555JAS.CXX_DATA values('PQR','*ERRD','CXR001',1,'1970-01-01',1,'ERRP','9999-12-31','C','00','R','H',08005,0,0,'N','','','2015-04-10-13.15.40.000000',90346); Commit;

But when it comes to mainframe as 79 bytes,

Insert into A5555JAS.CXX_DATA values('PQR','*ERRD','CXR001',1,'1970-01-01',1,'ERRP','9999-12-31','C','00','R','H',08005,0,0,'N','','','2015-04-10-13.15.40.000000',90346); Commit;


DB2 can't read data correctly since the column value for the date is split in to two lines,. Is there any way I could spread the data in two or three lines so that a coma will be the end of a line data and DB2 can read the data correctly?

Any suggestion will be greatly appreciated?

[prino]

Allocate a dataset with an LRECL > 79...

[tdanielxyz]

Hi Robert,
There are several hundred lines of data. So, if I fix one line , data from another record will have the same issue as the one with 79 bytes. Is there any way we could get a comma as the last byte but record length is < 79 bytes. If I use . 79 bytes there will be issues.

[Robert Sample]

There are several hundred lines of data

This is an EXTREMELY small amount of data -- millions (or even billions) of records are common on mainframes.

So, if I fix one line , data from another record will have the same issue as the one with 79 bytes

The BEST recommendation is to allocate a variable length blocked (VB) data set with a large enough record length to handle all the data -- if you don't know what that is, just use 27994 for the record length and 27998 for the block size. When you use a text transfer from the PC to the mainframe, the data will be transferred as it is on the PC (unless you have really long records) and you then can use the VB data set as input to DB2. Doing this is NOT handling a single record -- it is a generic solution for your problem.

[tdanielxyz]

//STEP010 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB0T)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
LIB('DBDCSUP.DB2.DB0T.RUNLIB.LOAD')
END
//SYSPRINT DD DSN=XXXXXX.XXXXX.XXXX.XXXXXXX.DT0525,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(300,600),RLSE),
// UNIT=(SYSDA,48)
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
INSERT INTO A5555JAS.CRC_DATA VALUES('PQR','*ERRD','MR001',1,
'1970-01-01',1,'ERRD','9999-12-31','C','00','R','H',08005,0,0,
'N','','','2015-04-10-13.15.40.000000',90346); COMMIT;
/*
When above SYSIN was used, the query was executed successfully
----------------------------------------------------------------------------------------

But for below query, it failed with below message
//SYSIN DD *
INSERT INTO A5555JAS.CXX_DATA VALUES('PQR','*ERRD','MR001',1,'1970-01
-01',1,'ERRD','9999-12-31','C','00','R','H',08005,0,0,
'N','','','2015-04-10-13.15.40.000000',90346); COMMIT;

***INPUT STATEMENT:
INSERT INTO A55555JAS.CXX_DATA VALUES('PQR','*ERRD','MR001',1,'1970-01
-01',1,'ERRD','9999-12-31','C','00','R','H',08005,0,0,
'N','','','2015-04-10-13.15.40.000000',90346);
SQLERROR ON INSERT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE 19
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOLTD SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -300 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFED4' X'00000000' X'00000000' X'FFFFFFF
INFORMATION

I also tried with 240 bytes of data (above insert statement in one line ) but SYSIN will not take more than 80 bytes.

[enrico-sorichetti]

when posting data learn to use the code tags
text surrounded by the code tags is displayed with a fixed font and preserves the spaces

with ...

Code: Select all

1234567890
*        *
1234567890


without

1234567890
* *
1234567890

help the people who spend their time helping You

[Robert Sample]

If your site uses JES2, then yes the SYSIN limit is 80 bytes. If your site uses JES3 this is not true. It appears that you will need to write a program in the language of your choice to change your input data so each line of the INSERT ends with a comma.

[steve-myers]

Another option is to do what PRINO and Robert Sample suggest; allocate a data set with a large LRECL, send just the data - not the JCL - to the mainframe. In the JCL, change

//SYSIN DD *

to

//SYSIN DD DISP=SHR,DSN=xxx

where xxx is the name of the data set containing the data.