Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Data in variable length PS file to be loaded into DB2 table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
neelus.s

New User


Joined: 05 Apr 2007
Posts: 4
Location: Minneapolis

PostPosted: Tue May 01, 2007 9:28 am    Post subject: Data in variable length PS file to be loaded into DB2 table
Reply with quote

Hi All,

I have a variable length PS file in which data is comma seperated. The data in this file has to be loaded into DB2 table.

Note: Some of the fields in this file are not having data.

Sample file:

00603,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00604,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00605,PR,Aguadilla,,Agu,18.25460052,-67.09100342
00607,PR,Aguas Buenas,,Agu,18.15320015,-66.06099701
00608,PR,Aguirre,,Agu,0,0
00609,PR,Aibonito,,Aib,18.08309937,-66.15499878
00610,PR,Anasco,,Ana,18.1704998,-67.08200073
00611,PR,Angeles,,Ang,18.17130089,-66.47499847
00612,PR,Arecibo,,Are,18.28280067,-66.42500305
00613,PR,Arecibo,,Are,18.28280067,-66.42500305
00615,PR,Arroyo,,Arr,17.58040047,-66.03399658
00616,PR,Bajadero,,Baj,18.25429916,-66.41000366

there are 7 fields in the above file.

I will be using Platinum load JCL or IBM load JCL to load data into the table. Basically i want to format this file before loading the table. Also giving be the table structure below.

ZIP_C CHAR(5)
ST_C CHAR(2)
CITY_N VARCHAR(35)
CNTY_N CHAR(35)
CITY_ABBR_N VARCHAR(3)
LATTD_I FLOAT(8)
LNGTD_I FLOAT(8)

Expecting replies as sonn as possible as i am badly stuck in this problem

Regards,
Neelima
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue May 01, 2007 9:49 pm    Post subject: Re: Data in variable length PS file to be loaded into DB2 ta
Reply with quote

If a table has a varchar field definition looks like:

Code:

EXEC SQL DECLARE TESTTBL TABLE                         
( ACCT_ID                        CHAR(19) NOT NULL,     
  MISC_TX                        VARCHAR(320) NOT NULL   
) END-EXEC.                                             


and COBOL DCLGEN will be something like this:

Code:

01  DCLTESTTBL.                                         
    10 ACCT-ID             PIC X(19).               
    10 MISC-TX.                                     
       49 MISC-TX-LEN      PIC S9(4) USAGE COMP.   
       49 MISC-TX-TEXT     PIC X(320).             



where MISC-TX-LEN is length of the variable text.

So you can format PS file in same format as DCLGEN of table. And use LOAD statement to load data. Note even though table has variable length fields, there is a maximum length of field. What this means is in load file there will be place holder for whole 320 bytes but there may be data in 320 bytes as defined by LEN parameter. And load file will be Fixed Block where total length can be derived from cobol DCLGEN structure.

Hope this helps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Passing data from REXX to C programme... chong.zhou CLIST & REXX 2 Wed Jul 26, 2017 9:11 pm
No new posts Passing data from REXX to C programme... chong.zhou All Other Mainframe Topics 0 Wed Jul 26, 2017 4:55 pm
No new posts Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm
No new posts SSH - known_hosts file configuration vasanthz All Other Mainframe Topics 2 Wed Jul 26, 2017 2:10 am
No new posts copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us