Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

is there an SQL version of a logical file with column renaming?

Posted on 2011-05-07
13
Medium Priority
?
683 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:stevebowdoin
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35714488
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?
0
 
LVL 5

Author Comment

by:stevebowdoin
ID: 35715723
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
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 300 total points
ID: 35716523
It may be possible to query the data dictionary to determine the short name (not sure). Try querying SYSIBM.SYSCOLUMNS and see what COLUMN_NAME and SYSTEM_COLUMN_NAME show and build a utility against that to update your code / column mapping.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:stevebowdoin
ID: 35716714
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
0
 
LVL 5

Author Comment

by:stevebowdoin
ID: 35717295
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 35724056
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 35725186
More thinking -- your AS/400 might be at an older release which could complicate things. What is the OS version/release?

Tom
0
 
LVL 27

Expert Comment

by:tliotta
ID: 35725324
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
0
 
LVL 5

Author Comment

by:stevebowdoin
ID: 35725774
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
0
 
LVL 27

Accepted Solution

by:
tliotta earned 600 total points
ID: 35733284
At i 7.1, if this is for a F-spec reference, have you investigated the ALIAS keyword? It should bring the "long names" in place of the common "short names". For new code, that might be useful.

I'm not clear on what your "key maintenance overhead" involves.

And I'm still not clear on any actual problem (other than what seems likely to be understandable headaches).

First, is your old code using native I/O or SQL? If it's native I/O, I might just develop a set of LF definitions that can be recompiled whenever name changes occur. I could always ensure that the field name will be CUSTC00001 even if it's been renamed to CUSTC00002 by DB2. The RENAME() keyword can be used. Each LF would need an explicit field list in order to ensure that the format identifier didn't change unless I changed the list myself.

Technically, it would be possible to automate the deletion of each LF, the updating of the source and the recreation of each LF to keep things in sync. If CUSTC00001 has been renamed, the compile should fail; so you'll probably be warned that any source needs fixing even if it's not automated.

Also, a LF can share/use an existing SQL access path (though the reverse won't likely be true). By compiling after any database changes, a keyed LF shouldn't even add to any index overhead for the system.

It might take some thought to come up with any actually good ideas. I'd already be irritated with a vendor who changed column names when making format changes especially if it's known that custom programming accesses the database. It's too easy to keep old names.

Add any comments you can think of to fill in gaps. There's bound to be better methods if enough is known.

Tom
0
 
LVL 36

Assisted Solution

by:Gary Patterson
Gary Patterson earned 600 total points
ID: 35734593
Steve,

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?


Can you explain in more detail what you mean by this?  I mean, you're creating an index.  You only specify key fields when you create an index in SQL.  Logicals let you combine the functions of  an SQL VIEW and an SQL INDEX, but you can't do that in SQL.

When you say "add" do you mean that you want to append new columns to the table?  If so, ALTER TABLE is what you're looking for in that case.

1) When you say "old code" do you mean pre-ILE OPM RPG?  ("RPG" source type)?  Any reason you can't just convert it (them) to RPG IV (CVTRPGSRC)?  I've converted hundreds of thousands of RPG III/RPG400 to RPV IV programs using this tool and others, and the vast majority convert with no issues.  This is far and away the best long-term solution, and the easiest to maintain.  This way you can just use the files the vendor sends you and forget about short names altogether.

2) If this is just a limited number of tables, write RPG copy books that contain the field rename I/O specs, and just update that copybook (or write a utility that creates the copybook) when you get a new version of the file and recompile the dependent programs.

3) If you can't convert to RPG IV for some reason, perhaps you can externalize the I/O for this file in your programs to an RPGLE that can handle the long field names.

4) Retrieve the SQL source for the file in question using Navigator (you can retrieve SQL even if it was created with DDS!), and recreate the table using SQL DDL (CREATE TABLE), specifying the short names that you want.  That way, when the vendor sends up a new file, you just repeat the process.


- Gary Patterson
0
 
LVL 5

Author Comment

by:stevebowdoin
ID: 35734956
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
0
 
LVL 5

Author Closing Comment

by:stevebowdoin
ID: 35769125
Thanks for the help.  At least I know there is not a better way that the one I found.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question