Posted: Sat May 23, 2015 10:02 am Post subject: Issue in Loading Data to DB2 from a PC note pad
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 separated 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?
There are several hundred lines of data to be loaded in DB2. Any suggestion will be greatly appreciated?
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Sat May 23, 2015 1:59 pm Post subject:
tdnj,
How are you inserting the rows in to the table? Running it via SPUFI or in batch using IKJEFT01? Either way you shouldn't have any problem as your data is limited to 80 bytes of sysin data.
It doesn't matter as to how many lines the data is split into. As long as your number of columns match the number of values you inserting it should be fine.
you need to show us the error you are getting when you execute that insert statement. _________________ Kolusu
www.linkedin.com/in/kolusu
I manually edited the SYSIN and when above SYSIN was used, the query was executed successfully
----------------------------------------------------------------------------------------
But for below query, it failed with below message
Code:
//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 D9331COZ.CRC_DATA VALUES('OEB','*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
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Sun May 24, 2015 3:18 pm Post subject:
tdnj,
The reason you are getting the -180 error is due to the date field being broken across lines. Are all your records split into a different line on the date field?
How are you uploading to the mainframe? FTP? or PCOMM transfer?
If you are using emulator transfer method , I suggest that you override the mainframe file LRECL to 133 , so that the lines do not get truncated. _________________ Kolusu
www.linkedin.com/in/kolusu
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Sun May 24, 2015 5:08 pm Post subject:
This is the third posting of this topic I have come across today - and I have only looked at 3 forums so far. I have locked on one the other was the original one that I looked at so left it. _________________ Utility and Program control cards are NOT, repeat NOT, JCL.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
