I am trying to work on a problem that I have to copy a FB file of unknown LRECL to an output file.
My tasks has follwoing steps:
Step 1) The file can be created using DB2 Unload, so the file LRECL will be defined automatically as FB.
Step 2) Create a sort step to uncompress the first field of the file. I can do that that by using p,m,f,TO=F (BI,To=ZD in my case) in outrec build, but I dont know the records length of rest of the file after that to be copied into output file. For a VB file i can specify 'p' to copy rest of the file, but that does nto work for FB where I need to specify p,m
Step 3) Send the file to off-host system which can read the zoned decimal format and rest of the data.
I want something similar to the follwoing for an FB file. As far as I know following is valid code only for a VB
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
LRECLs can't be FB, that's for RECFMs. They can't be "unknown" either.
Is what you mean that you'd like a "generalised" step which could run after your unload expanding the first two fields from a total of eight bytes to 14 and including all the other data undisturbed, irrespective of the LRECL that input happens to have?
If so, what I can think of for now is to "expand" the record with six useless byte after those first two fields when you do the unload.
The solution does not work for my problem. In my input I do not have the XXXXXX part of the input you specified. My input is as follows
Code:
//SORTIN DD *
11112222REST_OF_USEFUL_DATA
in the above sortin 1111 and 2222 are in binary format. These fields are expanded to new lengths in sortout. I want the sortout to have following format
Code:
Format for output
1111111122222222REST_OF_USEFUL_DATA
Above output is expanding first two fields Binary fields to specified length and retaining the rest of data as it is.
input 1111 BI expanded to Length 8 ZD as 11111111 in output
input 2222 BI expanded to Length 8 ZD as 22222222 in output
The solution does not work for my problem. In my input I do not have the XXXXXX part of the input you specified. My input is as follows
Code:
//SORTIN DD *
11112222REST_OF_USEFUL_DATA
in the above sortin 1111 and 2222 are in binary format. These fields are expanded to new lengths in sortout. I want the sortout to have following format
Code:
Format for output
1111111122222222REST_OF_USEFUL_DATA
Above output is expanding first two fields Binary fields to specified length and retaining the rest of data as it is.
input 1111 BI expanded to Length 8 ZD as 11111111 in output
input 2222 BI expanded to Length 8 ZD as 22222222 in output
Hope that calrifies the requirement.
One more thing just to clarify. I do not know the length of REST_OF_USEFUL_DATA in the above example since I am trying to make it a generic step for various FB files each with a different LRECL.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
virus wrote:
Hi,
I am trying to work on a problem that I have to copy a FB file of unknown LRECL to an output file.
My tasks has follwoing steps:
Step 1) The file can be created using DB2 Unload, so the file LRECL will be defined automatically as FB.
Step 2) Create a sort step to uncompress the first field of the file. I can do that that by using p,m,f,TO=F (BI,To=ZD in my case) in outrec build, but I dont know the records length of rest of the file after that to be copied into output file. For a VB file i can specify 'p' to copy rest of the file, but that does nto work for FB where I need to specify p,m
Step 3) Send the file to off-host system which can read the zoned decimal format and rest of the data.
Well are you aware that you can actually convert the binary and decimal values in DB2 Unload step itself? Look at the scalar functions CHAR or DIGITS which gives you a readable format of data. You don't even need sort to reformat the file once again. And please don't use terms like "uncompress" as it totally different from what you intend to do. You are merely reformatting the data to readable format.
Bill,
I am guess that OP is having a DB2 unload with Dynamic SQL where he chooses the specific columns to be unloaded or multiple tables and the unload output file LRECL depends on the number of columns selected or the table it is being unloaded and OP wants to use SORT to reformat them in a generic way.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Quote:
...what I can think of for now is to "expand" the record with six useless byte after those first two fields when you do the unload.
You could then use OVERLAY.
You didn't answer my question either, so now you have to tell us how come you don't know the record-length? Look at the output dataset from the unload, pop, there it is.
The contents of the first eight bytes are irrelevant, all you want to know is whether eight bytes can be expanded to 14 without having to know the length of the fixed-length record. What I suggested is to attempt the expansion when you do the unload - whether that is possible, I don't know. The data in the extra six bytes is not relevant. It can be immediately before the eight bytes, immediately after or anywhere within.
Other than that you could have a two-step approach, FTOVing the dataset in step 1, and then using your original approach in step 2.
This could probably be done with two ICETOOL operators in one step.
I have multiple(approx 80-90) table unloads followed by this sort step to reformat the fields. Now instead of trying to find length of each of the unload and then create a sort step based on its length would mean writing 80-90 JCLs. I was trying if a generic approach can be followed. I will still be ending up to create multiple JCLs for the unload card name, but can use the same proc to do rest of the tasks.
Kolusu,
I cannot expand the fields while downloading as I am using a using a tool to create multiple data cards for each table I have to unload. Tool just creates a
Code:
Select * from Table_Name
statement for each Table_Name in input file. This is the same as you have correctly mentioned in your comment while addressing to Bill.
I will try the option suggested by Bill using ICETOOL.
I will post result after trying, by that time if any one has any other suggestions, I won't mind.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
virus wrote:
Thanks Kolusu and Bill,
I have multiple(approx 80-90) table unloads followed by this sort step to reformat the fields. Now instead of trying to find length of each of the unload and then create a sort step based on its length would mean writing 80-90 JCLs. I was trying if a generic approach can be followed. I will still be ending up to create multiple JCLs for the unload card name, but can use the same proc to do rest of the tasks.
Kolusu,
I cannot expand the fields while downloading as I am using a using a tool to create multiple data cards for each table I have to unload. Tool just creates a
Code:
Select * from Table_Name
statement for each Table_Name in input file. This is the same as you have correctly mentioned in your comment while addressing to Bill.
I will try the option suggested by Bill using ICETOOL.
I will post result after trying, by that time if any one has any other suggestions, I won't mind.
Virus
As I said you are just wasting resources. You are unloading 80-90 tables and you are processing them all over again just to reformat. If you are not aware you can read the SYSIBM.SYSCOLUMNS and format the columns to be in readable format and generate the dynamic SELECT statement. Alternatively if you are always going to unload the full table why not create the Static SQL with all the numeric columns to a readable format?
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Well, if you can list the dataset attributes and extract the LRECL from that, then you can generate the Sort Control Cards for the step in question.
You could either do that "dynamically" (which would automatically account for table updates reflected in the unload) or as a "once off". It should be quicker to do a couple of little "somethings" to do that, rather than 80-90 sets of JCL and Control Cards, with at least one typo along the way.
If you are not aware you can read the SYSIBM.SYSCOLUMNS and format the columns to be in readable format and generate the dynamic SELECT statement.
I have not used that and was not aware. Let me search internet on how to do that.
Thanks.
Quote:
Well, if you can list the dataset attributes and extract the LRECL from that, then you can generate the Sort Control Cards for the step in question.
You could either do that "dynamically" (which would automatically account for table updates reflected in the unload) or as a "once off". It should be quicker to do a couple of little "somethings" to do that, rather than 80-90 sets of JCL and Control Cards, with at least one typo along the way.
Bill, sorry couldn't understand you quite well over there.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Kolusu's way is going to be better.
You might not know the LRECLs of all the datasets, but the system well.
Through various means you can make that information available in a way which can be processed.
You process that information, such that the processor now knows the LRECL. The processor continues and generates
Code:
OPTION COPY
INREC BUILD=(stuff)
stuff being the details on the Control Card to carry out the required task.
Those can be generated as a once-off (from your test database for instance. You could even generate all the JCL needed for it as well) or "on the fly", a JOB generated and sent to the INTRDR (or "somewhere" for "submission" by "something").
Get Kolusu's idea working. You have 80-90 tables of an unknown (to us) volume of data. The less you have to read the data, the better your throughput and costs are going to be.
the fields. Now instead of trying to find length of each of the unload and then create a sort step based on its length would mean writing 80-90 JCLs. I was trying if a generic approach can be followed. I will still be ending up to create multiple JCLs for the unload card name, but can use the same proc to do rest of the tasks.
