is there an SQL version of a logical file with column renaming?
I have a SQL table that contains column names that are more than 10 characters in length. DB2 is creating what it calls a “short name”. This short name is the first 5 characters of the name then a 5 digit sequence number. I am able to use the short name in my RPG.
I fear that the vendor will insert new columns that will change the short names. What was CUSTC00001 is now CUSTC00002. That will be a big problem.
I cannot rename the column in the table(PF).
In a DDS created logical file I can do a rename.
Is there a SQL object that will allow me to rename a column like a logical file?
Thanks
Steve
I fear that the vendor will insert new columns that will change the short names. What was CUSTC00001 is now CUSTC00002. That will be a big problem.
I cannot rename the column in the table(PF).
In a DDS created logical file I can do a rename.
Is there a SQL object that will allow me to rename a column like a logical file?
Thanks
Steve
mrjoltcola
Better practice is not to use system generated short names if you need short names at all. Since you say you cannot rename the columns, do you mean long names only? Can you work with the vendor on short names as part of their DDL?
ASKER
I can make no changes to base table. The vendor will not change their large system just for me. I have to deal with it as it is.
Thanks
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DSPFFD will display the short name and the long name as an ALIAS.
This morning i have found IBM documentation that tells me what i want is doable. I have not figured out the syntax yet.
Steve
This morning i have found IBM documentation that tells me what i want is doable. I have not figured out the syntax yet.
Steve
ASKER
Well this works:
create index bydopnitm on open_item
( INVOICE_NO_STRING as invnos, CUSTOMER_ID as custid,
REFERENCE_NUMBER as refno,
IS_SPLIT_BILL as split,
POST_MODULE as postmod,
RECORD_TYPE as rectype )
Rcdfmt oi add AMOUNT, AMOUNT_C, AMOUNT_D, AMOUNT_N,AMOUNT_R,
BILL_DATE, GL_DATE, ID,INVOICE_ID, INVOICE_NO, ORDER_ID, POST_KEY,
REMARK, SHIP_DATE, SOURCE
This command renames the columns alright. The only problem is it makes the renamed columns part of the key.
Does anybody know how to add columns with a new name that is not part of the key?
Steve
create index bydopnitm on open_item
( INVOICE_NO_STRING as invnos, CUSTOMER_ID as custid,
REFERENCE_NUMBER as refno,
IS_SPLIT_BILL as split,
POST_MODULE as postmod,
RECORD_TYPE as rectype )
Rcdfmt oi add AMOUNT, AMOUNT_C, AMOUNT_D, AMOUNT_N,AMOUNT_R,
BILL_DATE, GL_DATE, ID,INVOICE_ID, INVOICE_NO, ORDER_ID, POST_KEY,
REMARK, SHIP_DATE, SOURCE
This command renames the columns alright. The only problem is it makes the renamed columns part of the key.
Does anybody know how to add columns with a new name that is not part of the key?
Steve
Why is there a problem with using the long names? RPG can handle names 50 or more characters long.
Is it the amount of typing? Are you using WDSC (or later) or SEU? Using a newer IDE may help by having the IDE do a lot of the typing for you.
Tom
Is it the amount of typing? Are you using WDSC (or later) or SEU? Using a newer IDE may help by having the IDE do a lot of the typing for you.
Tom
More thinking -- your AS/400 might be at an older release which could complicate things. What is the OS version/release?
Tom
Tom
The only problem is it makes the renamed columns part of the key.
Why is that a problem? RPG doesn't need to use the field names for keys. A key-list only needs to match the data types and lengths of the key fields.
Can you show examples of any problematic RPG coding?
Tom
Why is that a problem? RPG doesn't need to use the field names for keys. A key-list only needs to match the data types and lengths of the key fields.
Can you show examples of any problematic RPG coding?
Tom
ASKER
Tom
Thanks for the response. I should have stated the release. It is as new as it gets... 7.1
I am and old dog. Still using SEU.
I have some old code i have to maintain. Using the data structs will slow me down. I will be using them in new code.
The only problem is have with the renamed columns in the key is the key maintenance overhead. The RPG does not care.
Steve
Thanks for the response. I should have stated the release. It is as new as it gets... 7.1
I am and old dog. Still using SEU.
I have some old code i have to maintain. Using the data structs will slow me down. I will be using them in new code.
The only problem is have with the renamed columns in the key is the key maintenance overhead. The RPG does not care.
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tom
I investigated LFs first. You cannot RENAME the “Alternative name”. You have to RENAME the short name. The vendor puts out two updates a year. If they do something that will cause short names to change then I will have to edit the LF source each time. That is not the kind of job security I am looking for.
The vendor lives in their Java world. We had to drag them kicking and screaming to put the application on the 400. They are doing a good job. I do not expect them to change their operation just for one installation.
By “key maintenance overhead” I mean the extra work the OS has to do because of a more complicated key. The more fields you have in the key the more work the system has to do.
Gary.
When I do DSPFFD BYDOII, it tells me that each of the fields in the key expression is now key fields in the LF.
As I stated to Tom, I cannot use the SQL name in the LF. I cannot create a DDS LF with the long names.
As for the copybooks, that would require editing if the vendor changes the file. Which they are app to do. Besides, how do I get “AR_REASON_CODE_ID” on to a screen or on a print out? Moves, redefines?? That creates maintenance problems.
From the early days of the control program facility, we all have all extoled the virtues of external files. I don’t want to back up to the System/36 days. The 400 way would be to solve this problem in an external file.
What I have with the index works great. I may have to be content with burning some DASD and a few cycles.
You two guys are the best resource I have. What I have may be as good as it gets.
Thanks.
I have included the DSPFD of the index and the DSPFFD of the base table.
DSPFD.pdf DSPFFD.pdf
I investigated LFs first. You cannot RENAME the “Alternative name”. You have to RENAME the short name. The vendor puts out two updates a year. If they do something that will cause short names to change then I will have to edit the LF source each time. That is not the kind of job security I am looking for.
The vendor lives in their Java world. We had to drag them kicking and screaming to put the application on the 400. They are doing a good job. I do not expect them to change their operation just for one installation.
By “key maintenance overhead” I mean the extra work the OS has to do because of a more complicated key. The more fields you have in the key the more work the system has to do.
Gary.
When I do DSPFFD BYDOII, it tells me that each of the fields in the key expression is now key fields in the LF.
As I stated to Tom, I cannot use the SQL name in the LF. I cannot create a DDS LF with the long names.
As for the copybooks, that would require editing if the vendor changes the file. Which they are app to do. Besides, how do I get “AR_REASON_CODE_ID” on to a screen or on a print out? Moves, redefines?? That creates maintenance problems.
From the early days of the control program facility, we all have all extoled the virtues of external files. I don’t want to back up to the System/36 days. The 400 way would be to solve this problem in an external file.
What I have with the index works great. I may have to be content with burning some DASD and a few cycles.
You two guys are the best resource I have. What I have may be as good as it gets.
Thanks.
I have included the DSPFD of the index and the DSPFFD of the base table.
DSPFD.pdf DSPFFD.pdf
ASKER
Thanks for the help. At least I know there is not a better way that the one I found.