DB2 Dataload from PC to mainframe issue



IBM's flagship relational database management system

DB2 Dataload from PC to mainframe issue

Postby tdanielxyz » Sat May 23, 2015 6:56 pm

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?
tdanielxyz
 
Posts: 7
Joined: Sun Oct 17, 2010 12:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Dataload from PC to mainframe issue

 

Re: DB2 Dataload from PC to mainframe issue

Postby prino » Sat May 23, 2015 7:49 pm

Allocate a dataset with an LRECL > 79...
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy

These users thanked the author prino for the post:
k singh (Thu May 28, 2015 2:06 am)
User avatar
prino
 
Posts: 536
Joined: Wed Mar 11, 2009 12:22 am
Location: Oostende, Belgium
Has thanked: 3 times
Been thanked: 21 times

Re: DB2 Dataload from PC to mainframe issue

Postby tdanielxyz » Sat May 23, 2015 8:14 pm

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.
tdanielxyz
 
Posts: 7
Joined: Sun Oct 17, 2010 12:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Dataload from PC to mainframe issue

Postby Robert Sample » Sun May 24, 2015 12:09 am

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

Re: DB2 Dataload from PC to mainframe issue

Postby tdanielxyz » Sun May 24, 2015 2:36 am

//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.
tdanielxyz
 
Posts: 7
Joined: Sun Oct 17, 2010 12:06 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Dataload from PC to mainframe issue

Postby enrico-sorichetti » Sun May 24, 2015 2:52 am

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 ...
1234567890
*        *
1234567890


without

1234567890
* *
1234567890

help the people who spend their time helping You :mrgreen:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: DB2 Dataload from PC to mainframe issue

Postby Robert Sample » Sun May 24, 2015 5:10 am

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

Re: DB2 Dataload from PC to mainframe issue

Postby steve-myers » Mon May 25, 2015 3:45 am

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.

These users thanked the author steve-myers for the post:
k singh (Thu May 28, 2015 2:06 am)
steve-myers
Global moderator
 
Posts: 1886
Joined: Thu Jun 03, 2010 6:21 pm
Has thanked: 4 times
Been thanked: 197 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post