Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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.
---+----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 ?
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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?
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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.
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.
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
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. . .
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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(-).
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.
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
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)
Joined: 12 Jan 2010 Posts: 27 Location: Pleasanton,CA
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.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
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(*)
...
...
..)
