MVSFORUMS.com :: View topic - loading Text files into DB2 tables

through

#1: loading Text files into DB2 tables Author: Jashu, Location: chennai

Posted: Thu Mar 13, 2003 1:06 am
—
Hi

I have got data from the client in TEXT files.I don't have the SYSPUNCH(position and others).Each colmns is seperated by delimiter in text files.

How to load the data into tables from the text files.What should I give in the SYSIN.

regards

JASHU

#2: Author: kolusu, Location: San Jose

Posted: Thu Mar 13, 2003 11:38 am
—
Jashu,

There are 2 ways of doing it.
Option:1

Run the text file thru sort/easytrieve to remove the delimiter and just load the data using
the following control card

Code:

LOAD DATA INDDN(SYSREC00) REPLACE YES
INTO TABLE TABLENAME

Option:2

Unload the table which you are trying to load so that it will generate a load card.use a where clause in the sql with primary key > 999999999 , so that it will not unload any rows.

Code:

//STEP0100 EXEC PGM=IKJEFT01
//*
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXXX.RUNLIB.LOAD')
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=YOUR LOAD CARD FILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=CYL,(1,1),RLSE)
//SYSIN DD *
SELECT *
FROM
TABLE NAME
WHERE PRIMARY KEY > '999999999999'
;
/*

Now the syspunch data set will have something like this

Code:

LOAD DATA INDDN(SYSREC00) REPLACE YES
INTO TABLE TABLENAME
(COL1 POSITION (1:3) CHAR(3),
COL2 POSITION (4:39) CHAR(36),
COL3 POSITION (40:45) CHAR(6),
COL4 POSITION (46:48 ) CHAR(3),
COL5 POSITION (49:64) CHAR(16) )

Now you can edit this syspunch dataset by incrementing the start position and load the table using the edited control cards.

Hope this helps...

cheers

kolusu

#3: Author: Manas Biswal, Location: Chennai, India

Posted: Thu Mar 13, 2003 11:24 pm
—
Hi all,

The question by Jashu got me thinking. Even in very small DBMS systems like MS-Access, we have got the option to load a table from a flat file using delimiters. Isn't it odd that in a well established RDBMS like DB2, you do not have the option to load a table using delimiters. Logically, a delimited file should be enough to load into a table without any load card. I, of course do not know whether the same is possible in other popular RDBMS like Oracle or Sybase.

Masters ==> What are your thoughts on this?

Regards,
Manas

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1