Solved

How to find Keys in AS/400 Logical File with SQL

Posted on 2012-03-15
19
3,786 Views
Last Modified: 2012-06-27
Does anyone know how to determine what the key field(s) are for a logical file on an AS/400 from a query?

I am linking from MS Access with VBA and I need to specify the keys in order to update.
0
Comment
Question by:EE_GLW
  • 6
  • 4
  • 3
  • +3
19 Comments
 
LVL 32

Expert Comment

by:shalomc
Comment Utility
Do you have access to a 5250 telnet session? You will have to run the DSPFD command.
Unfortunately, logical files are not listed in the SYSKEYS catalog table...
0
 

Author Comment

by:EE_GLW
Comment Utility
Yes, I can see the keys on a green screen.  If that program can get to the keys, why can't I?
What is dspfld using to get that info?
0
 
LVL 13

Expert Comment

by:_b_h
Comment Utility
Use DSPFD on SYSKEYS to see the CREATE statement for it, and copy it.
In the WHERE clause, remove the DBIATR = 'IX' part of the selection to create your own view (or select).
Sample:
CREATE VIEW SYSKEYSmy (INDEX_NAME FOR COLUMN IXNAME, INDEX_OWNER  
FOR COLUMN IXCREATOR, COLUMN_NAME FOR COLUMN COLNAME,                
COLUMN_POSITION FOR COLUMN COLNO, ORDINAL_POSITION FOR COLUMN        
COLSEQ, ORDERING, INDEX_SCHEMA FOR COLUMN IXDBNAME,                  
SYSTEM_COLUMN_NAME FOR COLUMN SYS_CNAME, SYSTEM_INDEX_NAME FOR      
COLUMN SYS_IXNAME, SYSTEM_INDEX_SCHEMA FOR COLUMN SYS_IDNAME) AS    
SELECT DBILFI, VARCHAR(STRIP(DBIOWN),128), DBILFL, INTEGER(DBIPOS),  
INTEGER(DBKPOS), DBKORD, DBKLB2, DBKFLD, DBKFIL, DBKLIB FROM        
QSYS.QADBIFLD, QSYS.QADBKFLD WHERE DBIFIL = DBKFIL AND DBILIB =      
DBKLIB AND DBKFLD = DBIFLD                

Another alternative is to use an API for DSPFD, such as Retrieve Database File Description (QDBRTVFD).

Hope this helps
Barry
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
SYSCOLUMNS in QSYS2 contains a list of columns in a table (physical-file), and SYSINDEXES contains a list of all the indexes with some details about each of them (like the number of keys), but I'm not aware of any table that contains the key fields in an index (or logical-file).

HTH,
DaveSlash
0
 

Author Comment

by:EE_GLW
Comment Utility
_h_h

It looks like I would need to get a list of the indexes for a given logical and then go to SYSKEYSmy to find the fields.

But first, I looked through SYSKEYSmy, and the field name that I know is the one I am looking for is not in there.

Is that because of shalomc's statement "Unfortunately, logical files are not listed in the SYSKEYS catalog table..."
0
 

Author Comment

by:EE_GLW
Comment Utility
DaveSlash-

It looks like sysindexes only contains physical file indexes.  I do not see any logical keys in there.
0
 
LVL 13

Expert Comment

by:_b_h
Comment Utility
I tested the SYSKEYSmy view on my system and found the key fields for a logical file.

9....+...40....+...41....+...42....+...43. | ....+...52....+...53....+...54....+...55....+...56....+...57....+...58....+...59....+.
 COLUMN_POSITION  ORDINAL_POSITION  KEY    |                                                     FIELD       FILE        LIBRARY  
                                    ORDER  |                                                     NAME        NAME        NAME      
                                           |                                                                                      
              3                 1     A    |                                                     GAME        USERLF      STR8TS    
             10                 2     A    |                                                     USERCARD    USERLF      STR8TS    
             11                 3     A    |                                                     USERTIME    USERLF      STR8TS
0
 
LVL 13

Expert Comment

by:_b_h
Comment Utility
Next time i will preview before posting :)
COL_POS  ORD_POS  KEY            FIELD         FILE          LIBRARY  
                               ORDER        NAME         NAME       NAME
 3                 1             A            GAME          USERLF      STR8TS    
10                2             A            USERCARD   USERLF      STR8TS    
11                3             A            USERTIME    USERLF      STR8TS
0
 

Author Comment

by:EE_GLW
Comment Utility
Barry-

Can you help me connect the dots?

What do I do with SysKeysMy to get fields like FileName and LibraryName?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Accepted Solution

by:
_b_h earned 500 total points
Comment Utility
DBKFLD is field name, DBKFIL is file name, DBKLIB is library name
You can use DSPFFD Display File Field Description to see the field names:
DSPFFD SysKeysMy

You should also get the system admin's permission to do this as well; the view may impact the procedures for future operating system upgrades.

Barry
0
 

Author Comment

by:EE_GLW
Comment Utility
Barry-

You are the man!  Thanks for your patience.


-- Return Key(s) on Logical
Select DBKFIL "File name"
, DBKFMT "Format name"
, DBKFLD "Field name"
, DBKPOS  "Position of field in_key"
, DBKORD "A-Ascending, D-Descending"
, DBKATR "S-Signed,U-Unsigned,A-Absolute,D-Digits,Z-Zoned"
, DBKFMP "Position of format in file"
, DBKLB2 "Library name"
, DBXDEVKEY  "Derived Key Definition"
From QSYS.QADBKFLD
where dbklib = [Your Library]
and dbkfil = [Your File]
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
I suspect that this is a LF created with DDS. There wouldn't be any CREATE VIEW statement available in that case. There probably won't be useful entries in SYSKEYS either. ...As ShalomC previously mentioned.

From a green-screen session, you can use the DSPFD command to see LF keys:
DSPFD FILE(LibraryName/FileName) TYPE(*ACCPTH)

Open in new window

Put the actual library and file names into the FILE() parameter. The key fields, if any exist, will be listed under the Access Path description.

Technically, you can retrieve the values from the system database cross-reference:
SELECT DBKLIB, DBKFIL, DBKFLD, DBKPOS, DBKORD, DBKATR
 FROM QSYS/QADBKATR
 WHERE DBKLIB ='MYLIB' AND DBKFIL ='MYLF'

Open in new window

Because the QADBKATR file is itself a logical file (similar to, but not exactly the same as a 'view'), it's relatively safe to query.

By programming, the safest and essentially best way is to use the Retrieve Database File Description (QDBRTVFD) APIb to retrieve info about a database file. But that gets complicated real fast.

You can also use commands in programs to generate a temporary file:
DSPFD FILE( MYLIB/MYLF )
      TYPE(*ACCPTH)
      OUTPUT(*OUTFILE)
      OUTFILE( SOMELIB/NEWFILE )

Open in new window

After that command runs, there will be a file with one row for each key field from file MYLF in MYLIB. The rows will be in a file named NEWFILE in library SOMELIB.

Tom
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
Ah, well... Barry has it covered. Never mind...

Tom
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Building views over IBM DB2 system catalog files is really not recommended.  If you have to do it, you might consider dropping it after you use it.

Here is one "supported" mechanism for obtaining this information:

Use the DSPFD TYPE(*ACCPTH) OUTFILE() command and drop the key list to a temporary table, then query the temporary table.  You can execute system command remotely calling g the QCMDEXC stored procedure.

QCMDEXC parameters:

1) String containing the command to execute
2) 15.5 decimal number containing the length of the command

CALL QCMDEXC ('DSPFD LIBRARY/FILENAME) TYPE(*ACCPTH) OUTFILE(QGPL/DSPFDOUT)' 0000000060.0000)

Then just query the file (QGPL/DSPFDOUT) you created to get the list of key fields.

Make sure you use a unique file name, and it is polite to clean up after yourself by dropping the temp table after you are done with it.

- Gary Patterson
0
 

Author Comment

by:EE_GLW
Comment Utility
Gary-

Is there a problem with running sql statements off of files in QSYS?
What is the issue?
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Many of the QSYS files are designed or OS and DBMS use, and are critical to the proper operation of the system.  Improperly accessing these files or modifying or locking records could have consequences to OS or DB stability.

Can you safely query the QADBKFLD file?  Probably.  

Do I recommend it?  Only if there is no other supported interface available to get what you need.

IBM provides documented, supported interfaces to obtain this information through a CL command and a system API - those will be the safest ways to get the data you want.

- Gary Patterson
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
Is there a problem with running sql statements off of files in QSYS?

Not if nothing goes wrong. Otherwise, yes.

Generally, access (read-only!) over logical files is okay. Just get in, and get out quick. Those are DB2 system files, and you don't want to be holding any locks on them that can be avoided.

Best advice is never to access the physical files. Use DSPDBR to determine potential logical files or views that present data you might need. And then get what's needed and release the file.

Tom
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now