Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Insert incremental record using Icetool

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

New User


Joined: 12 Apr 2008
Posts: 36
Location: NOIDA

PostPosted: Tue Oct 25, 2011 10:35 pm    Post subject: Insert incremental record using Icetool
Reply with quote

Hi ,

I have unload file with records like :

10/25/20111072404363417
10/25/20112625004417717
10/25/20111072404361717
10/25/20111072404361917
10/25/20111072404362517

I want to append a unique rowid for each record with a hardcoded value
like 'FIL01' along with the record id(seq no of record),
So that the O/P file will look like.

FIL0100000000000000110/25/20111072404363417
FIL0100000000000000210/25/20112625004417717
FIL0100000000000000310/25/20111072404361717
FIL0100000000000000410/25/20111072404361917
FIL0100000000000000510/25/20111072404362517

Does anybody how to do this using DFSORT or ICETOOL.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Oct 25, 2011 10:41 pm    Post subject: Reply to: Insert incremental record using Icetool
Reply with quote

Yes, I'm sure someone does.

Have a look at the manual/search forum for BUILD, SEQNUM and you can probably get there quite quickly yourself.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Oct 25, 2011 11:06 pm    Post subject:
Reply with quote

CHANDAN KAPOOR,
What is the bigger picture for this requirement, if this is unload, I am wondering why not do it in unload itself?

Regardless, see if below helps... I am assuming 80 byte FB input and output will be 100 byte FB.
Code:
//STEP0001 EXEC PGM=SORT                       
//SORTIN   DD  *                               
10/25/20111072404363417                         
10/25/20112625004417717                         
10/25/20111072404361717                         
10/25/20111072404361917                         
10/25/20111072404362517                         
//SORTOUT  DD  SYSOUT=*                         
//SYSIN DD *                                   
   SORT FIELDS=COPY                             
   OUTREC BUILD=(C'FIL01',SEQNUM,15,ZD,1,80)   
/*                                             
//SYSOUT DD SYSOUT=*                           
//*                                             

OUTPUT
Code:
FIL0100000000000000110/25/20111072404363417
FIL0100000000000000210/25/20112625004417717
FIL0100000000000000310/25/20111072404361717
FIL0100000000000000410/25/20111072404361917
FIL0100000000000000510/25/20111072404362517

Thanks,
Back to top
View user's profile Send private message
CHANDAN KAPOOR

New User


Joined: 12 Apr 2008
Posts: 36
Location: NOIDA

PostPosted: Tue Oct 25, 2011 11:47 pm    Post subject: Reply to: Insert incremental record using Icetool
Reply with quote

Hi ,

Can you suggest me that how to take care of this in uload itself.
Our unload file is VB , What things need to be changed in the
sortcard for the VB record.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 25, 2011 11:56 pm    Post subject:
Reply with quote

Hello,

Quote:
What is the bigger picture for this requirement, if this is unload, I am wondering why not do it in unload itself?

I suspect the assumption is that by "unload" you were talking about unloading data from a database table. If this is true, you would change the database unload, not the sort.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Oct 26, 2011 12:10 am    Post subject:
Reply with quote

CHANDAN KAPOOR,
Below is for the VB input file.

Code:
//SYSIN DD *                                   
   SORT FIELDS=COPY                             
   OUTREC BUILD=(1,4,C'FIL01',SEQNUM,15,ZD,5)   
/*                                             

What is your DB2 version?

Dick,
Yes I was referring to DB2 unload.

Thanks,
Back to top
View user's profile Send private message
CHANDAN KAPOOR

New User


Joined: 12 Apr 2008
Posts: 36
Location: NOIDA

PostPosted: Wed Oct 26, 2011 12:35 am    Post subject: Reply to: Insert incremental record using Icetool
Reply with quote

Hi ,

The query used for Unload is

SELECT DISTINCT I.INQR_GEN_KEY,CURRENT_DATE
FROM HUM.TIQOINQ I
INNER JOIN HUM.TIQTEAM T
ON T.TEAM_ID=I.OWNER_TEAM_ID
INNER JOIN HUM.TIQIMTM M
ON M.TEAM_ID=I.OWNER_TEAM_ID AND
M.INVENTORY_TYPE_CD='UW'
WHERE I.INQR_GEN_KEY=I.INQA_GEN_KEY AND
I.STATUS_CD IN('OP','CL') AND
I.INQR_TYPE_CD = 'KM' AND
I.INQA_TYPE_CD = 'KM' AND
((T.TPOINT_IND = 'Y' AND
DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= T.TPOINT_FREQ) OR
(DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= M.TEAM_SLA) OR
(T.CLOSE_LOOP_IND = 'Y' AND
DATE(INQUIRY_CLOSE_TS) =
CURRENT_DATE - 5 DAYS ))
WITH UR;

The DB2 version is V9R1M0 , Is there any way to generate a
(hardcoded value +sequence no) in unload itself as we are doing in sort.

Thanks,
Chandan Kapoor
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Oct 26, 2011 2:04 am    Post subject:
Reply with quote

CHANDAN KAPOOR,
See if below untested helps...

Small world icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif

Code:
SELECT CAST ('FIL01' || DIGITS(CAST(ROW_CNT AS DECIMAL(15,0))) AS
CHAR(20)) AS FIL,T.INQR_GEN_KEY,CURRENT_DATE
FROM (                                                           
   SELECT DISTINCT I.INQR_GEN_KEY,ROW_NUMBER () OVER () AS ROW_CNT
   FROM HUM.TIQOINQ I
   INNER JOIN HUM.TIQTEAM T
   ON T.TEAM_ID=I.OWNER_TEAM_ID
   INNER JOIN HUM.TIQIMTM M
   ON M.TEAM_ID=I.OWNER_TEAM_ID AND
   M.INVENTORY_TYPE_CD='UW'
   WHERE I.INQR_GEN_KEY=I.INQA_GEN_KEY AND
   I.STATUS_CD IN('OP','CL') AND
   I.INQR_TYPE_CD = 'KM' AND
   I.INQA_TYPE_CD = 'KM' AND
   ((T.TPOINT_IND = 'Y' AND
   DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
   = T.TPOINT_FREQ) OR
   (DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
   = M.TEAM_SLA) OR
   (T.CLOSE_LOOP_IND = 'Y' AND
   DATE(INQUIRY_CLOSE_TS) =
   CURRENT_DATE - 5 DAYS ))) T
ORDER BY FIL
WITH UR;

Thanks,
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts ICETOOL - RESIZE DYNAMICALLY kranthikumarb DFSORT/ICETOOL 0 Tue Nov 21, 2017 5:30 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Squeeze record left so that zeroes ar... Vignesh Sid SYNCSORT 5 Fri Nov 10, 2017 1:40 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us