IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Merge column of different length to a row


IBM Mainframe Forums -> CLIST & REXX
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Fri Mar 12, 2010 5:18 pm
Reply with quote

I have following requiremnts :-

1) INCLUDE A FIXED VALUE HEADER(HHHHHHHHHHHHH)
2) Merge Columns to row with comma separation,insert hardcoded value(IBMMAIN) in front of first row
3) INSERT TRAILER GIVING COUNT OF RECORD MERGED
4) INSERT FIXED VALUE RECORD AT END(TTTTTTTTTTTTT)

Input file(VB,RL=11)
Content of input file

XLERK2
XLKBCS
XBA
XCSCLK
XCSSUP
XAXUPD
XICBCK
XLOBAL

EXPECTED Ouput file(VB,RL=34)
cONTENT OF O/P FILE
HHHHHHHHHHHHH
IBMMAIN,XLERK2,XLKBCS,XBA,XCSC
LK,XCSSUP,XAXUPD,XICBCK,XLOBAL
COUNT|8
TTTTTTTTTTTTT

Please let me know in case more info is required.
Thanks in adavnce
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Mar 12, 2010 10:47 pm
Reply with quote

Quote:
2) Merge Columns to row with comma separation,insert hardcoded value(IBMMAIN) in front of first row


The "rules" for "merging" columns to rows seems quite complex.

It appears that you want to prepend a string, squeeze out blanks, add comma delimiters, and split the result into 30 byte lines even if that means splitting the original data across lines (e.g. XCSCLK).

I don't see a way to do something that complex with DFSORT. I'd suggest you write a program or exit with the required logic.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Fri Mar 12, 2010 11:06 pm
Reply with quote

Can we do it using REXX ??
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Mar 12, 2010 11:09 pm
Reply with quote

You should ask that in the "CLIST & REXX" Forum. However, I'd suggest explaining the "rules" for merging columns to rows in more detail so people can understand exactly what you want to do.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Fri Mar 12, 2010 11:29 pm
Reply with quote

Frank ,

I think i have mentioned all the detail above . Only thing we can skip here is
splitting the original data across lines (e.g. XCSCLK).

Can we acheive this ?
Please let men know in case more info is required

Rgrds
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Mar 12, 2010 11:50 pm
Reply with quote

Quote:
I think i have mentioned all the detail above .


Not really. You are starting out with an input file that has VB/11 records (so 7 bytes of data max). You are ending up with an output file that has VB/34 (so 30 bytes of data max). The rules for getting from input to output are unclear.

Are you taking a specific number of input records and combining them for output (e.g. every 4 records)? Or are you trying to fit all the bytes possible in each output record (taking into account the added 'IBMMAIN' string, the length of the data (e.g. XBA is only 3 bytes), and the added delimiters)? Or what? Unless you can explain/simplify the rules, I can't help you.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Sat Mar 13, 2010 12:06 am
Reply with quote

Dear Frank ,

There is no specific number on input record It can be n number of rows in the input file.
The max length of the each record is 7 byte
Yes we are trying to fit all the possible bytes in a row .
We can take output file Record length to 130 bytes(VB) or 126 Bytes(FB).

Hope i am clear this time.
Rgrds
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Mar 13, 2010 12:12 am
Reply with quote

Hello,

Quote:
Hope i am clear this time.
Unfortunately not. . .

Keep in mind that while your requirement/rules are completely obvious to you, they may not be so clear to others who read the topic.

Quote:
We can take output file Record length to 130 bytes(VB) or 126 Bytes(FB).
How does this relate to the original example of 30-byte output?

If the length can be flexible, why split a value across output records?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Mar 13, 2010 12:18 am
Reply with quote

shr_amar,

What you've said leads me back to my original conclusion:

Quote:
I don't see a way to do something that complex with DFSORT. I'd suggest you write a program or exit with the required logic.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Sat Mar 13, 2010 12:22 am
Reply with quote

Dick ,

I understand that probably i am not able to explain the requirement . Original to make think more easier i have taken shorter RL of 30-byte.

So if we have solution for this shorter file i can customize according to my need.

I apologies if i have created confusion.

But the actual requirement is that o/p will be of 130 bytes (VB)

Please let me know what other things you guys are not clear

Rgrds
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Mar 13, 2010 12:46 am
Reply with quote

Hello,

Quote:
I apologies if i have created confusion.
Not to worry - we can usually get back on track icon_smile.gif

Quote:
So if we have solution for this shorter file i can customize according to my need.
Understood.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Sat Mar 13, 2010 1:27 am
Reply with quote

Dick ,

Why you have moved this post to REXX. I am looking solution using SORT or ICETOOL.

I think its better if we move it back to DFSORT forum, So that people can response

Regrds
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Sat Mar 13, 2010 1:33 am
Reply with quote

I thought this topic belonged in the DB2 forum since the topic header mentions columns of different lengths and rows, which only data bases have. Files have records, never rows, and columns is a term not typically associated with files -- and when so associated, a column is always a single byte of data, never more. Much of IT is based upon precise terminology and confusing rows with records is about as bad as mixing up generations and versions in a GDG -- both indicate the person writing has no real understanding of the terms.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Sat Mar 13, 2010 1:39 am
Reply with quote

I hope FRANK have understood the requirement and will provide us solution using DFSORT.

Rgrds
Back to top
View user's profile Send private message
MBabu

Active User


Joined: 03 Aug 2008
Posts: 400
Location: Mumbai

PostPosted: Sat Mar 13, 2010 1:42 am
Reply with quote

in rexx, read in the data while counting the lines. If there is not much data, read it into a stem using EXECIO and the number of lines will be in stem.0

Then concatenate the lines using IBMMAIN, and commas as needed using space() or strip() then rewrite the header, the new line and line count and the trailer. As you write out the constructed new line, use substr() as needed to break the line into record length chunks.

This should be a pretty straight forward rexx routine if my interpretation of the requirement is accurate.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Mar 13, 2010 1:53 am
Reply with quote

Quote:
I hope FRANK have understood the requirement and will provide us solution using DFSORT.


I guess you missed my previous post where I said:

Quote:
What you've said leads me back to my original conclusion:

I don't see a way to do something that complex with DFSORT. I'd suggest you write a program or exit with the required logic.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Sat Mar 13, 2010 2:40 am
Reply with quote

I know I am going to be sorry but...

Quote:

Can we do it using REXX ??


Here is an UNTESTED but FREE Rexx Exec to do what you need.

I hope shr_amar can use the slightest amount of is own brain power, along with help from co-workers to get the JCL required to test this code.
(I could have provided it, but lets see if he can do anything).

Code:

/* REXX EXEC TO STRING FIELDS TO ROWS */
/* THIS IS ASSUMING VB INPUT WITH THE DATA IN COLUMN 5 LENGTH OF 7  */
/* OUTPUT WILL BE FB 126 ; A FULL WORD WILL NOT BREAK IN THE MIDDLE */
/* THERE ARE NO ALLOCATE COMMANDS, ASSUMING JCL WILL BE USED */

/* INITIALIZE VARIABLES */
INREC. = ''
OUTREC. = ''

/* READ INPUT FILE INTO STEM VARIABLE */
"EXECIO * DISKR INFILE (STEM INREC. FINIS"

/* PUT HEADER IN OUTPUT STEM VARIABLE */
OUTREC.1 = 'HHHHHHHHHHH'

/* SET UP START OF FIRST DATA RECORD */
O = 2
OUTREC.O = 'IBMMAIN,'
LEN_OUT = 8

/* PROCESS EACH RECORD OF INPUT FILE  */
DO I = 1 TO INREC.0
  DATA = STRIP(SUBSTR(INREC.I,5,7))||',' /* ADD COMMA TO END */
  LEN1 = LENGTH(DATA)
  /* MAKE SURE WE DO NOT WRAP AROUND */
  IF LEN1 + LEN_OUT > 126 THEN DO
    O = O + 1
    LEN_OUT = 0
  END
  /* CONCATENATE OUTPUT RECORD */
  OUTREC.O = OUTREC.O||DATA
  LEN_OUT = LEN_OUT + LEN1
END

/* PUT TRAILERS IN OUTPUT STEM VARIABLE */
O = O + 1
OUTREC.O = 'COUNT'||'='||INREC.0
O = O + 1
OUTREC.O = 'TTTTTTTTTTT'

/* WRITE OUTPUT FROM STEM VARIABLE */
"EXECIO * DISKW OUTFILE (STEM OUTREC. FINIS"

EXIT
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 128
Location: UK

PostPosted: Mon Mar 15, 2010 1:05 pm
Reply with quote

Guys ,

From Frank note I have understood that probably record spillting is not possible using DFSORT. I am able to change the requirement which is as follows

I have following requiremnts :-

1) INCLUDE A FIXED VALUE HEADER(HHHHHHHHHHHHH)
2) Merge all records in the input file to a single row with comma separation,insert hardcoded value(IBMMAIN) in front of data record
3) INSERT TRAILER GIVING COUNT OF RECORD MERGED
4) INSERT FIXED VALUE TRAILER RECORD AT END(TTTTTTTTTTTTT)

Input file(VB,RL=11)
Content of input file

XLERK2
XLKBCS
XBA
XCSCLK
XCSSUP
XAXUPD
XICBCK
XLOBL

EXPECTED Ouput file(FB,RL=100)
CONTENT OF O/P FILE
HHHHHHHHHHHHH
IBMMAIN,XLERK2,XLKBCS,XBA,XCSCLK,XCSSUP,XAXUPD,XICBCK,XLOBL
COUNT|8
TTTTTTTTTTTTT

Rules are as follows :-

Each record will be maximum of 7 byte
Total number of record will be maximum of 10
Record should be merged in a single row.
Last record of the input file is a space which should be ommited.

Dick - I feel that this we can acheive using DFSORT . Can we move this querry back to DFSORT. So the expert can revert back.
Back to top
View user's profile Send private message
Pedro

Global Moderator


Joined: 01 Sep 2006
Posts: 2546
Location: Silicon Valley

PostPosted: Mon Mar 15, 2010 10:00 pm
Reply with quote

Quote:
Record should be merged in a single row.

I think you have a mis-understanding of what 'merge' means.

From an (older) DFSORT manual:
Quote:
Generally, the reason for merging data sets is to add more records to a data set that is already sorted.

For example, assume that the bookstore data set is already sorted by course department and book title (as shown in Figure 9), and you want to update it by merging it with a data set that contains five new records, also sorted by course department and book title.


To me, it is just another type of sort, but I supposed the algorithm for merging two already sorted datasets is different than for a merge of unsorted records.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> CLIST & REXX

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 9
No new posts PARSE Syntax for not fix length word ... JCL & VSAM 7
No new posts How to load to DB2 with column level ... DB2 6
No new posts VB to VB copy - Full length reached SYNCSORT 8
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top