Page 1 of 1

Column to rows using tool

PostPosted: Thu Oct 22, 2009 7:03 am
by akanchu
Hi,
I have a file with 500K records/id's in it. The record length of that file is 20 bytes.
File looks something like this :
1234561234567895000
0534789215432418940
9856214487995320043
6663332222111155571
1111205656500066310
2400000123563514004
.
.
.



I would like to convert these records in 1 column to rows. Each row should max have 32K id's in it.
the output would look like
1234561234567895000,0534789215432418940,9856214487995320043,6663332222111155571,1111205656500066310,2400000123563514004...
4235650212157458214,5421045450045875213,...
.
.
.



Is there some way I can do this thru utility eg SORT/FILEAID etc. If it is some other tool, then it will have to be moved to that forum.
we have SYNCSORT FOR Z/OS 1.3.1.0R version in our shop.


Let me know if any questions.

Thanks
akanchu.

Re: Column to rows using tool

PostPosted: Wed Oct 28, 2009 1:44 am
by dick scherrer
Hello,

Each row should max have 32K id's in it.
Suggest you speak with your storage management people and ask what is the largest blksize you can write for the output media intended. I suspect it will not be 32k * 20 . . .

Re: Column to rows using tool

PostPosted: Wed Oct 28, 2009 9:05 pm
by akanchu
Hi,
The limit of 32K has been set by me and nothing do storage management.
I can reduce it too, but not more than that.

The reason I have kept the cutoff, is that - after this processing, I will be using the output to a different step for SQL query proessing.
Less than 32K is fine too.

Thanks
akanchu.

Re: Column to rows using tool

PostPosted: Thu Oct 29, 2009 2:45 am
by dick scherrer
Hello,

I will be using the output to a different step for SQL query proessing.
How might the sample output posted be used with SQL?

Someone may post a "solution for Syncsort" or you might simply use Easytrieve (if you have it) or COBOL.

Re: Column to rows using tool

PostPosted: Thu Oct 29, 2009 9:36 pm
by akanchu
Hi,
COBOL is my backup plan. I was checking if we could do via utils.

//STEP1 EXEC PGM=SORT/FILEAID/ICETOOL
//SYSOUT DD SYSOUT=*                   
//INPUT DD DSN=FILE1,DISP=SHR 
//OUTPUT DD DSN=OUTPUT.FILE1,
.
.
//STEP2 - puts brackets in the start and end of the content of the OUTPUT.FILE1 and create OUTPUT.FILE2

//STEP4 EXEC PGM=IKJEFT01
//SYSTSIN  DD *                                         
  DSN  SYSTEM(DB1)                                     
  RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')     
/*                                                       
//SYSIN    DD *                                           
  SELECT *                     
  FROM   DEPARTMENT D
  WHERE  D.DEPT_ID IN                         
//       DD DSN=OUTPUT.FILE2,DISP=SHR
.
.
.



Hope it made sense.
Thanks.

Re: Column to rows using tool

PostPosted: Fri Oct 30, 2009 1:29 am
by dick scherrer
Hello,

What you want to do "makes sence" from a requirements perspective. Implementation will be another issue. . .

Suggest you step back and look at some other approach. Something like read a qsam record and write the needed database info to the output dataset. . .