• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5801
  • Last Modified:

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

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
EE_GLW
Asked:
EE_GLW
  • 6
  • 4
  • 3
  • +3
1 Solution
 
shalomcCTOCommented:
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
 
EE_GLWAuthor Commented:
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
 
_b_hCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
EE_GLWAuthor Commented:
_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
 
EE_GLWAuthor Commented:
DaveSlash-

It looks like sysindexes only contains physical file indexes.  I do not see any logical keys in there.
0
 
_b_hCommented:
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
 
_b_hCommented:
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
 
EE_GLWAuthor Commented:
Barry-

Can you help me connect the dots?

What do I do with SysKeysMy to get fields like FileName and LibraryName?
0
 
_b_hCommented:
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
 
EE_GLWAuthor Commented:
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
 
tliottaCommented:
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
 
tliottaCommented:
Ah, well... Barry has it covered. Never mind...

Tom
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
EE_GLWAuthor Commented:
Gary-

Is there a problem with running sql statements off of files in QSYS?
What is the issue?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
tliottaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 6
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now