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
 

 

Split Records into 2 lines based on criteria

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sarbamrit

New User


Joined: 15 Oct 2007
Posts: 12
Location: Kentucky

PostPosted: Wed Sep 21, 2011 1:40 pm    Post subject: Split Records into 2 lines based on criteria
Reply with quote

I'm having data in below format in input file (Max length 100)

1. ID - 9 Length Fixed START POSITION 1
2. First Name - 15 Max Variable Length START POSITION 11
3. Last Name - 22 Max Variable Length (This can have space between text) START POSITION 28
4. In Date - 10 Length Fixed START POSITION 55
5. Num1 - 9 Digits Fixed START POSITION 65
6. Num2 - 11 Fixed (May not be available in all cases) START POSITION 76
7. Out Date - 10 Length Fixed START POSITION 89
Sample:
111111111 FIRSTNAME LAST NAME 09/20/2011 123456789 11133344455 09/21/2011

I'm trying to build an output file (max length 80) using above input file.

INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','11133344455','09/21/2011');

INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','','09/21/2011');
The second sample output is when Num2 is not available

In the above output file i need to take care that no trailing spaces are getting inserted into the table AAA.TABLENAME.

Would appreciate if anyone can help me out of this !
Back to top
View user's profile Send private message

kratos86

Active User


Joined: 17 Mar 2008
Posts: 148
Location: Anna NGR

PostPosted: Wed Sep 21, 2011 2:57 pm    Post subject:
Reply with quote

You can use PARSE feature to achieve the same. Lot of examples are available in our forum. If you face any issues let us know.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Sep 21, 2011 3:36 pm    Post subject:
Reply with quote

Just curiosity how input file is generated? Is it system generated?

I guess little thought on this bit will make your job easier
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Sep 21, 2011 4:29 pm    Post subject: Reply to: Split Records into 2 lines based on criteria
Reply with quote

sarbamrit,

I think you might have over-complicated it for yourself, or for us. You fields all have fixed start positions. You have two fields which could contain trailing blanks (and I suppose, potentially, could be entirely blank) and one which is a fixed length of 11, but which may be blank.

For those three fields, all trailing blanks are to be removed, even if the output for that field is only then represented by ''.

So, a simple "remove trailing blanks" question, plus putting in the quotes.

What is not so simple, or perhaps so simply dealt with, is the fact that your input requires splitting, which is only referred to in your title.

You have:

Code:
INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','11133344455','09/21/2011');


To avoid having to think about how to calculate for the split always working, and to generalise the solution somewhat, would this be acceptable output for you?

Code:
INSERT
  INTO AAA.TABLENAME
    VALUES
       ('111111111',
        'FIRSTNAME',
        'LAST NAME',
        '09/20/2011',
        '123456789',
        '11133344455',
        '09/21/2011');


Or better:

Code:
INSERT
  INTO AAA.TABLENAME
    VALUES
       (
        '111111111',
        'FIRSTNAME',
        'LAST NAME',
        '09/20/2011',
        '123456789',
        '11133344455',
        '09/21/2011'
        );
 
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Sep 21, 2011 6:01 pm    Post subject:
Reply with quote

we are building an INSERT statement and not a load file.

unfortunately, in db2 '' and ' ' are considered spaces when dealing with CHAR columns.

so, any CHAR columns with 0-length would have to have NULL instead of ''. (or ,,)

but, if the column is NOT NULL, then '' for spaces must be used.
unless of course the column attributes are NOT NULL WITH DEFAULT,
in which case you could use ,,

numeric columns CAN NOT have quotes around the values,

and again the rules for NOT NULL and NOT NULL WITH DEFAULT,
need to be followed.

if any column is NOT NULL but without a WITH DEFAULT,
then you need to supply some value.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Sep 22, 2011 3:51 am    Post subject:
Reply with quote

Amrit,

Quote:
The second sample output is when Num2 is not available


I took this to mean that the Num2 field is blank. Given that, here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=...  input file
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file
//TOOLIN DD *
COPY FROM(IN) TO(T1) USING(CTL1)
COPY FROM(T1) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  OUTFIL BUILD=(C'INSERT INTO AAA.TABLENAME VALUES (''',1,9,
      C''',''',
      11,10,JFY=(SHIFT=LEFT,TRAIL=C''',',LENGTH=12),
      81:C'1',/,
      28,10,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C'''',LENGTH=12),X,
      55,10,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C'''',LENGTH=12),X,
      65,9,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C'''',LENGTH=11),X,
      76,11,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C'''',LENGTH=13),X,
      89,10,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C''');',LENGTH=14),
      81:C'2')
/*
//CTL2CNTL DD *
  INREC IFOUTLEN=80,
    IFTHEN=(WHEN=(81,1,CH,EQ,C'2'),
      OVERLAY=(1,80,SQZ=(SHIFT=LEFT,MID=C',',PAIR=APOST)))
/*
Back to top
View user's profile Send private message
sarbamrit

New User


Joined: 15 Oct 2007
Posts: 12
Location: Kentucky

PostPosted: Thu Sep 22, 2011 2:50 pm    Post subject: Split Record
Reply with quote

Thanks a Ton! Frank. I used your JCL and it worked !!! You saved my day ! icon_biggrin.gif
Back to top
View user's profile Send private message
sarbamrit

New User


Joined: 15 Oct 2007
Posts: 12
Location: Kentucky

PostPosted: Thu Sep 22, 2011 2:52 pm    Post subject: Thanks to all !
Reply with quote

Sorry i missed others like kratos86, Escapa (Sambhaji), Bill and Dick who took their time out to reply to my message.
Thanks to you guys !
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Sep 22, 2011 2:53 pm    Post subject:
Reply with quote

You know what you are doing next with all this stuff? It has been suggested to me that you'd be much better using a load utility rather than inserts. What have you got to counter that argument?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Sep 22, 2011 3:01 pm    Post subject:
Reply with quote

Happy that you got your solution..

But my point is whatever way this file is generated why not generate comma separated quoted files at first place.

like...

Code:
"fld1","fld2 with space","fld3","","fld4"

And then use load utility...

Much faster and better approach..
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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