Hi, query for table structure?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
chechir
Member
Posts: 3
Joined: Wed Mar 12, 2008 7:01 pm

Hi, query for table structure?

Post by chechir » Thu Mar 13, 2008 5:46 pm

Hi,

i am begginer in db2 and i have limited access to database, only querys. I need to get the table structure through query, the names and types of the fields for a specific table.

Thanks in advance.

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Fri Mar 14, 2008 12:21 am

Hi Chechir,

Give this query a try.

Code: Select all

  
  SELECT NAME                                                           
        ,COLNO                                                          
        ,COLTYPE || '('                                                 
                 || CAST(LENGTH AS CHAR(5)) || ','                      
                 || CAST(SCALE AS CHAR(5)) || ')'                       
        ,CASE WHEN NULLS = 'N'                                          
             THEN 'NOT NULL' ELSE ' ' END                               
        ,TBNAME                                                         
    FROM SYSIBM.SYSCOLUMNS                                              
   WHERE TBCREATOR = 'TEST'            -- < OR 'TEST'                   
--   AND &#40;NAME = 'YOUR_COL_NAME'      -- < COLUMN NAME \ ONLY USE ONE 
     AND &#40;TBNAME    = YOUR_TBL_NAME'  -- < TABLE NAME  /          
          &#41;                                                             
     ORDER BY TBNAME, COLNO                                             
  ;       
Be sure to only use NAME or TBNAME, not both. You can probably remove TBCREATOR, but if you do you might want to ORDER by it also

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Fri Mar 14, 2008 12:32 am

HI Again,

Here's a query for the indexes on the table

Code: Select all

  SELECT KEYS.IXNAME                                          
        ,IX.UNIQUERULE                                        
        ,KEYS.COLNAME                                         
        ,KEYS.COLNO                                           
        ,KEYS.COLSEQ                                          
        ,IX.TBNAME                                            
    FROM SYSIBM.SYSINDEXES    IX,                             
         SYSIBM.SYSKEYS       KEYS                            
   WHERE IX.TBNAME          = 'YOUR_TBL_NAME'            
     AND KEYS.IXNAME        = IX.NAME                         
   ORDER BY IX.TBNAME,                                        
            KEYS.IXNAME,                                      
            KEYS.COLSEQ                                       

chechir
Member
Posts: 3
Joined: Wed Mar 12, 2008 7:01 pm

Post by chechir » Mon Mar 24, 2008 5:38 pm

Thanks a lot!!!

This is very useful for me, and i am seeing other tables like SYSIBM.SYSTABLES.
But i dont understand why the tables appear with a diferent name in the SQL and the aplication that i use for download the data (cwbtf.exe). For example the table BIBLIO.CLIENTES_DIRECCION in SYSIBM.SYSTABLES is "BIBLIO.DIRCLI" in cwbtf.exe.

Cheers!

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Tue Mar 25, 2008 8:50 pm

The difference in names is most probably due to one being the actual table name and the other an alias for the table. This is not an uncommon practice. There are functions that you can perform on the actual table but cannot on the alias. Kind of a security feature of sorts.

Dave

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others