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

Syncsort Help


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Tue Feb 09, 2010 11:56 pm
Reply with quote

Hello everybody,
I have a need to prepare a file with individual columns as fixed length so that BMC utility can be used by DBA for their DBA load on to the DB2 tables.

Input file I am using has columns of variable length.

I have used the PARSE control statement to accomplish the same with a small amount of instream data pulled from my input file.The delimiter in the input file is showing as "." which is hex '05' which I have to retain in case of columns that are short.

I am posting my job here with the output.

Code:
//PS010   EXEC PGM=SYNCSORT                                             
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN DD *                                                           
PI85W5AGH7.U5140.U70367...                                             
PIXMX43T33.4287959.9433597...                                           
PI6CB6QTW7.U757419.U823286.U96453..                                     
PIND0A1218.8284391.U1062231.U73551.U845008.                             
PIND0A1218.8284391.U1062231.U73551.U845008.                             
PIX38FR2H3.7227279.7943026.U643478.U647131.U673673                     
/*                                                                     
//SORTOUT DD SYSOUT=*                                                   
//SYSIN    DD    *                                                     
   OPTION COPY                                                           
   INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=10),                           
                          %02=(ENDBEFR=C'.',FIXLEN=7),                             
                          %03=(ENDBEFR=C'.',FIXLEN=7),           
                          %04=(ENDBEFR=C'.',FIXLEN=7),           
                          %05=(ENDBEFR=C'.',FIXLEN=7),           
                          %06=(FIXLEN=7)),                       
    BUILD=(%01,%02,%03,%04,%05,%06)                   
/*                                                     


Output for the above job is

Code:
---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
********************************* TOP OF DATA *********************************
PI85W5AGH7U5140  U70367                                                         
PIXMX43T3342879599433597                                                       
PI6CB6QTW7U757419U823286U96453                                                 
PIND0A12188284391U106223U73551 U845008                                         
PIND0A12188284391U106223U73551 U845008                                         
PIX38FR2H372272797943026U643478U647131U673673                                   
******************************** BOTTOM OF DATA ********************************


Now this is where I need help. Is there any way I can preinsert a space before each column on my output.This is one requirement for DXT load.

to make it clear this is how I want out put to be


SPACE COL1 SPACE COL2 SPACE COL3

I wonder if there is a way I can insert a space using the BUILD statement ?

Thank you..
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Wed Feb 10, 2010 3:09 am
Reply with quote

I am not sure I did the right thing. But output looks ok to me when i tried playing around the BUILD. Here is what I did on the BUILD statement I posted in my first posting

BUILD=(X,%01,X,%02,X,%03,X,%04,X,%05,X,%06)

I am also pasting my output


Code:
 COMMAND INPUT ===>                                            SCROLL ===> CSR 
---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
********************************* TOP OF DATA **********************************
 PI85W5AGH7 U5140   U70367                                                     
 PIXMX43T33 4287959 9433597                                                     
 PI6CB6QTW7 U757419 U823286 U96453                                             
 PIND0A1218 8284391 U106223 U73551  U845008                                     
 PIND0A1218 8284391 U106223 U73551  U845008                                     
 PIX38FR2H3 7227279 7943026 U643478 U647131 U673673                             
******************************** BOTTOM OF DATA ********************************


If anybody thinks I did the right thing..well and good and thank you.
If not please suggest me what I am doing wrong?
Back to top
View user's profile Send private message
Escapa

Senior Member


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

PostPosted: Wed Feb 10, 2010 11:51 am
Reply with quote

Yes its correct. I havent tested(as I have dfsort at my shop) but even this should work.
Code:

//SYSIN    DD    *                                                     
   OPTION COPY                                                           
   INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=10),                           
                          %02=(ENDBEFR=C'.',FIXLEN=8),                             
                          %03=(ENDBEFR=C'.',FIXLEN=8),           
                          %04=(ENDBEFR=C'.',FIXLEN=8),           
                          %05=(ENDBEFR=C'.',FIXLEN=8),           
                          %06=(FIXLEN=7)),                       
    BUILD=(2:%01,%02,%03,%04,%05,%06)                   
/*                                               
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Wed Feb 10, 2010 9:09 pm
Reply with quote

Thanks Sambhaji.I will try out the one you mentioned and let you know the outcome.

Is there any way I can populate those columns that doesn't have values(currently column 3,4 in most records shown above are spaces) with a value hyphen(-) rather than spaces as it currently is.
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 1:48 am
Reply with quote

Dear Sambhaji,

I tried the control card you mentioned above which is

BUILD=(2:%01,%02,%03,%04,%05,%06)

Looks to me it is only inserting a blank column before the first data field.

What I am looking for is a blank column before each data fields (say)

If there are five columns in the input dataset I am expecting an output
that looks something like

Blank Data-fld1 blank Data-fld2 blank Data-fld3 blank Data-fld4 blank Data-fld5

I am pasting the output Iam getting for the control card you specified

---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
********************************* TOP OF DATA **********************************
PI85W5AGH7U5140 U70367
PIXMX43T3342879599433597
PI6CB6QTW7U757419U823286U96453
PIND0A12188284391U106223U73551 U845008
PIND0A12188284391U106223U73551 U845008
PIX38FR2H372272797943026U643478U647131U673673
******************************** BOTTOM OF DATA ********************************

Thanks again for the help. i will stay with the one I tried which I believe is working unless someone says the syntax I used is not the right one.

I am still trying to figure out the answer to the problem I have on replacing space filled empty (nullable) data fields with hyphen(-).Per IBM manual it is possible with IF THEN.But it is not saying how can it be done with the BUILD parameter.
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: Thu Feb 11, 2010 2:26 am
Reply with quote

Hello,

Your original "test output" appears correct with the possible exception of multiple blanks when data does not completely fill the field. . . This would depend on the requirement.

Quote:
I am still trying to figure out the answer to the problem I have on replacing space filled empty (nullable) data fields with hyphen(-).Per IBM manual it is possible with IF THEN.But it is not saying how can it be done with the BUILD parameter.
This appears to be a different question.

If it is, please start a new topic for the new question. If it is not a new question, some clarification is needed here. . .
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 2:55 am
Reply with quote

Thanks Dick..

Well actually I am working on the same file.What I was trying to do is

(1) come up with a mechanism where I can transform the input file that has data fields of variable length to fixed length so that BMC utility can take it for load on to DB2 tables.

I was able to achieve the above with PARSE control cards.

(2) My second task was to insert a blank column (space) before each data fields.

I was able to achieve the same with minor modifications to the BUILD.

(3) My final task is to insert a hypen to the output of above task 2 in their empty data fields.

To make it more clear this is how my output data looks like now.It has six data fields.

First data field is a key field which has a field length of 10 bytes.This field is not a null field on the DB2 table when it is inserted.

Data field 2 until 6 - all of them has a field length of 7 bytes each.But these data fields can be empty on the file. When empty I want to replace space on that data field with hypen(-).

File currently looks like this

Command ===> Scroll ===> CSR
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 PI85W5AGH7 U5140 U70367
000002 PIXMX43T33 4287959 9433597
000003 PI6CB6QTW7 U757419 U823286 U96453
000004 PIND0A1218 8284391 U106223 U73551 U845008
000005 PIND0A1218 8284391 U106223 U73551 U845008
000006 PIX38FR2H3 7227279 7943026 U643478 U647131 U673673
****** **************************** Bottom of Data ****************************

What I am trying to achieve as expected output is
Command ===> Scroll ===> CSR
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 PI85W5AGH7 U5140 U70367
000002 PIXMX43T33 4287959 9433597 - - -
000003 PI6CB6QTW7 U757419 U823286 U96453 - -
000004 PIND0A1218 8284391 U106223 U73551 U845008 -
000005 PIND0A1218 8284391 U106223 U73551 U845008 -
000006 PIX38FR2H3 7227279 7943026 U643478 U647131 U673673
****** **************************** Bottom of Data ****************************

What I am trying to do is modify the control cards mentioned below which I have used to accomplish the first two tasks in order to complete the third task.

000019 //SORTOUT DD SYSOUT=*
000020 //SYSIN DD *
000021 OPTION COPY
000022 INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=10),
000023 %02=(ENDBEFR=C'.',FIXLEN=7),
000024 %03=(ENDBEFR=C'.',FIXLEN=7),
000025 %04=(ENDBEFR=C'.',FIXLEN=7),
000026 %05=(ENDBEFR=C'.',FIXLEN=7),
000027 %06=(FIXLEN=7)),
000028 BUILD=(X,%01,X,%02,X,%03,X,%04,X,%05,X,%06)
000029 /*



Hope I explained my requirement thoroughly.If you still suggest me open a new requirement, I will be more than happy to do so.


Thanks again,
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: Thu Feb 11, 2010 4:38 am
Reply with quote

Hello,

Quote:
If you still suggest me open a new requirement, I will be more than happy to do so
I believe this is good where it is icon_smile.gif

What release of Syncsort is being used?
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 4:51 am
Reply with quote

Hello DS,

Syncsort version is


SYNCSORT FOR Z/OS 1.3.2.1R
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Feb 11, 2010 5:24 am
Reply with quote

Hi,

do you want all fields populated with a hyphen ?

Why isn't there a hyphen on first line of output ?

Gerry
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 6:13 am
Reply with quote

Gerry,

I want only those data fields that has no value in it to be populated with hypen.

To be clear

If the original file has three datafields first data field is 10 bytes and second datafield is 7 bytes and third data field is 7 bytes but it is spaces or blank which looks like

---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+--
12345678901234567

BMC utility requires the files to be in this format in order to load to db2 tables

space on first column(pos 1)
pos 2-11 - data field 1
space on pos 12
pos 13-19 - data field 2
space on pos 20
pos 21-27 - data field 3

But if there is no value for data field 3 on the file rather than having spaces on pos 21-27 it should be a hypen.

Hope my explanation is clear enough.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Feb 11, 2010 6:21 am
Reply with quote

Hi,

this may help
Code:
   OPTION COPY                                                         
   INREC PARSE=(%01=(ENDBEFR=C'.',FIXLEN=10),                           
                          %02=(ENDBEFR=C'.',FIXLEN=7),                 
                          %03=(ENDBEFR=C'.',FIXLEN=7),                 
                          %04=(ENDBEFR=C'.',FIXLEN=7),                 
                          %05=(ENDBEFR=C'.',FIXLEN=7),                 
                          %06=(FIXLEN=7)),                             
    BUILD=(X,%01,X,%02,X,%03,X,%04,X,%05,X,%06)                         
 OUTREC IFTHEN=(WHEN=(13,1,CH,EQ,C' '),                                 
               OVERLAY(13:C'-',21:C'-',29:C'-',37:C'-',45:C'-')),       
        IFTHEN=(WHEN=(21,1,CH,EQ,C' '),                                 
               OVERLAY(21:C'-',29:C'-',37:C'-',45:C'-')),               
        IFTHEN=(WHEN=(29,1,CH,EQ,C' '),                                 
               OVERLAY(29:C'-',37:C'-',45:C'-')),                       
        IFTHEN=(WHEN=(37,1,CH,EQ,C' '),                                 
               OVERLAY(37:C'-',45:C'-')),                               
        IFTHEN=(WHEN=(45,1,CH,EQ,C' '),                                 
               OVERLAY(45:C'-'))                                       


Gerry
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 6:32 am
Reply with quote

Gerry,
Here is more explanation

Original file looks like

Data field 1 - pos 1-10
Data field 2 - pos 11-17
Data field 3 - pos 18-24

First and foremost requirement of BMC utility is to have a prepared with a delimiter space prefixed before each data field as an identification tag.So it should look like

space on first column(pos 1)
pos 2-11 - data field 1
space on pos 12
pos 13-19 - data field 2
space on pos 20
pos 21-27 - data field 3

That is why there is a space on the first column as you asked.This task is already handled.So there is no concern on that.

Second Requirement is if there is no value for data field 3 on the file rather than having spaces on pos 21-27 it should be a hypen.

Output should look like
space on first column(pos 1)
pos 2-11 - data field 1
space on pos 12
pos 13-19 - data field 2
space on pos 20
pos 21 - hypen (as an identification tag for data field with empty data value. so rather than inserting a space on the db2 table hypen will be inserted for this nullable column that has empty data value)
pos 22--27 - space
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 6:33 am
Reply with quote

Thanks Gerry, I will try and let you know.

Prem
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 6:36 am
Reply with quote

Gerry,

Perfect. It is working. Thank you so much and appreciate all your help.

Code:
Pasting the output here

---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8-
********************************* TOP OF DATA **********************************
 PI85W5AGH7 U5140   U70367  -       -       -                                   
 PIXMX43T33 4287959 9433597 -       -       -                                   
 PI6CB6QTW7 U757419 U823286 U96453  -       -                                   
 PIND0A1218 8284391 U106223 U73551  U845008 -                                   
 PIND0A1218 8284391 U106223 U73551  U845008 -                                   
 PIX38FR2H3 7227279 7943026 U643478 U647131 U673673                             
******************************** BOTTOM OF DATA ********************************
Code'd
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Feb 11, 2010 6:49 am
Reply with quote

Hi,

good to hear it's working.

Try using code when showing output.

Gerry
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 7:57 am
Reply with quote

Thanks Gerry.

will do.I saw code form but I dint know that does the trick for displaying in the correct MF format.

I will keep that in mind next time I post it.

Thanks again.

To the moderators: This topic can be closed as resolved.

Thanks everyone.
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: Thu Feb 11, 2010 9:39 am
Reply with quote

Good to hear it is working - thank you for letting us know icon_smile.gif

d
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Feb 11, 2010 9:54 am
Reply with quote

May be it's too late. Does n't BMC LOAD support loading a DB2 table when the load file is delimited by some character? I remember having been able to load even if the fields are not in fixed postions but delimited by some fixed character.(FORMATCSV with TERMINATEDBY option if I remember correctly)
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 10:42 am
Reply with quote

Dear Arun Raj,

I share the same feeling you have on the BMC load utility.But I am not a DBA nor I have access to BMC loader.DBA in my shop keeps saying it is not possible.Until some time ago we were writing cobol programs to achieve the purpose whenever we have a requirement of the similar nature.

If you think there is a way we can do it,please provide me with the helpful job or some form of reference links so I can take it with DBA and debate on this.

Thanks again,
Prem
Back to top
View user's profile Send private message
Escapa

Senior Member


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

PostPosted: Thu Feb 11, 2010 11:01 am
Reply with quote

Premkumar wrote:
Per IBM manual it is possible with IF THEN.But
it is not saying how can it be done with the BUILD parameter.

What you read there may not work in your shop..
Actually this is not the place you should look for. You need to see SYNCSORT manuals.
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 11:18 am
Reply with quote

Sambhaji,

The topic in debate is resolved with Gerry's help.IFTHEN and OVERLAY did the trick.

Thank you and everyone for the valuable contributions.

I have requested moderators to mark the topic as "Resolved" in order to avoid further confusion.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Feb 11, 2010 11:56 am
Reply with quote

PremKumarNair,

Do you have a manual?. If you dont have one, you can get it easily from the bmc website. The manual has a clear explanation of all the options with examples.
Here is a sample BMC Load card which uses a comma separated file as input. If the input is not comma(",") separated one, I guess you might need to add the "TERMINATED BY" parameter to the FORMATCSV option to specify the delimiter. And also you will have to make sure that the none of the data has the delimiter character(dot-"." in your case)
Code:
LOAD DATA  INDDN SYSREC01 REPLACE
             ERRDDN SYSERR                   
             DELETEFILES NO                   
             DISCARDS 0                       
             IFDISCARDS 5                     
             LOG NO NOCOPYPEND               
             UPDATEDB2STATS YES BMCSTATS YES 
             WORKDDN SYSUT1                   
             REDEFINE YES                     
             FORMAT CSV ENCLOSEDBY '' (You might need a TERMINATED BY "." here)       
INTO TABLE  XXXXXXXX.TABLE_NAME           
(                                             
 COL_1               POSITION(*)             
,COL_2               POSITION(*)             
,COL_3               POSITION(*)
...
...
..)
Back to top
View user's profile Send private message
PremKumarNair

New User


Joined: 12 Jan 2010
Posts: 27
Location: Pleasanton,CA

PostPosted: Thu Feb 11, 2010 12:06 pm
Reply with quote

Thanks Arun.Appreciate your help.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Feb 11, 2010 2:44 pm
Reply with quote

You're welcome icon_smile.gif
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
No new posts DFSORT/SYNCSORT/ICETOOL JCL & VSAM 8
No new posts Syncsort "Y2C" Function SYNCSORT 1
No new posts Arithmetic division using Syncsort SYNCSORT 6
Search our Forums:

Back to Top