Syncsort Help

PremKumarNair · Posted: Tue Feb 09, 2010 11:56 pm

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.

PremKumarNair · Posted: Wed Feb 10, 2010 3:09 am

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

Escapa · Posted: Wed Feb 10, 2010 11:51 am

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

PremKumarNair · Posted: Wed Feb 10, 2010 9:09 pm

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.

PremKumarNair · Posted: Thu Feb 11, 2010 1:48 am

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.

dick scherrer · Posted: Thu Feb 11, 2010 2:26 am

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.

PremKumarNair · Posted: Thu Feb 11, 2010 2:55 am

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,

dick scherrer · Posted: Thu Feb 11, 2010 4:38 am

Hello,

PremKumarNair · Posted: Thu Feb 11, 2010 4:51 am

Hello DS,

Syncsort version is

SYNCSORT FOR Z/OS 1.3.2.1R

gcicchet · Posted: Thu Feb 11, 2010 5:24 am

Hi,

do you want all fields populated with a hyphen ?

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

Gerry

PremKumarNair · Posted: Thu Feb 11, 2010 6:13 am

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.

gcicchet · Posted: Thu Feb 11, 2010 6:21 am

Hi,

this may help

PremKumarNair · Posted: Thu Feb 11, 2010 6:32 am

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

PremKumarNair · Posted: Thu Feb 11, 2010 6:33 am

Thanks Gerry, I will try and let you know.

Prem

PremKumarNair · Posted: Thu Feb 11, 2010 6:36 am

Gerry,

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

gcicchet · Posted: Thu Feb 11, 2010 6:49 am

Hi,

good to hear it's working.

Try using code when showing output.

Gerry

PremKumarNair · Posted: Thu Feb 11, 2010 7:57 am

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.

dick scherrer · Posted: Thu Feb 11, 2010 9:39 am

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

d

Arun Raj · Posted: Thu Feb 11, 2010 9:54 am

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)

PremKumarNair · Posted: Thu Feb 11, 2010 10:42 am

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

Escapa · Posted: Thu Feb 11, 2010 11:01 am

PremKumarNair · Posted: Thu Feb 11, 2010 11:18 am

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.

Arun Raj · Posted: Thu Feb 11, 2010 11:56 am

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)

PremKumarNair · Posted: Thu Feb 11, 2010 12:06 pm

Thanks Arun.Appreciate your help.

Arun Raj · Posted: Thu Feb 11, 2010 2:44 pm

You're welcome