Solved

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

Posted on 2012-03-15
19
4,345 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 33

Expert Comment

by:shalomc
ID: 37729341
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
ID: 37729485
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
ID: 37729556
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 18

Expert Comment

by:daveslash
ID: 37729690
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
ID: 37729714
_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
ID: 37729722
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
ID: 37729989
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
ID: 37730017
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
ID: 37730209
Barry-

Can you help me connect the dots?

What do I do with SysKeysMy to get fields like FileName and LibraryName?
0
 
LVL 13

Accepted Solution

by:
_b_h earned 500 total points
ID: 37731038
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
ID: 37731267
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
ID: 37731271
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
ID: 37731278
Ah, well... Barry has it covered. Never mind...

Tom
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 37731298
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
ID: 37731310
Gary-

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

Expert Comment

by:Gary Patterson
ID: 37731330
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 37737460
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
ID: 37745669
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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