Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: Column names via odbc

    can someone please help me construct an sql statement to retrieve just the column names for a specific table? The examples i've found so far don't work.

    thanks!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The informaiton is in syscat.columns. It is fairly straight forward. The DB2 catalog views are documented in the SQL Reference Vol 1, (appendicies).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    Quote Originally Posted by curtmorehouse View Post
    can someone please help me construct an sql statement to retrieve just the column names for a specific table? The examples i've found so far don't work.

    thanks!
    DB2 V8 z/os

    select name
    from sysibm.syscolumns
    where tbname = ucase('Your_table_name_not_qlfd')


    For example:

    Code:
    select name as ".......NAME.........." from sysibm.syscolumns
    where tbname = ucase('SUB_RED_HY')
    order by 1
    Result:

    .......NAME..........
    ACT_CD
    ACT_ALT
    ACT_BRN_CD
    SECURITY_ID

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a typical exercise in a class. So have a look at the DB2 catalog tables, and you should be able to handle it from there by yourself.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2008
    Posts
    34

    solved

    None of the examples given worked for me, maybe I have an older OS?
    Anyway, I did get it to work using PHP, and the code is below for anyone looking to retrieve column names via php with os/400...

    Code:
    	//SQL quyery  
    $Query = "SELECT * from iesfile.drivers";
    
    //execute query 
    $queryexe = odbc_do($connectionstring, $Query);
    
     //query database
    while(odbc_fetch_row($queryexe)) 
        {
        	
        //collect results 
        
        $numfields = odbc_num_fields($queryexe);
        
          for($i = 1;$i <= odbc_num_fields($queryexe);$i++)
        {
            $odbc['rows']['fieldnames'][$i] = odbc_field_name($queryexe,$i);
            $odbc['rows']['fieldtypes'][$i] = odbc_field_type($queryexe,$i);
            $odbc['rows']['fieldlengths'][$i] = odbc_field_len($queryexe,$i);
        }
        unset($i);
            
              }

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Is it DB2

    Quote Originally Posted by curtmorehouse View Post
    None of the examples given worked for me, maybe I have an older OS?
    Anyway, I did get it to work using PHP, and the code is below for anyone looking to retrieve column names via php with os/400...

    Code:
    	//SQL quyery  
    $Query = "SELECT * from iesfile.drivers";
    
    //execute query 
    $queryexe = odbc_do($connectionstring, $Query);
    
     //query database
    while(odbc_fetch_row($queryexe)) 
        {
        	
        //collect results 
        
        $numfields = odbc_num_fields($queryexe);
        
          for($i = 1;$i <= odbc_num_fields($queryexe);$i++)
        {
            $odbc['rows']['fieldnames'][$i] = odbc_field_name($queryexe,$i);
            $odbc['rows']['fieldtypes'][$i] = odbc_field_type($queryexe,$i);
            $odbc['rows']['fieldlengths'][$i] = odbc_field_len($queryexe,$i);
        }
        unset($i);
            
              }
    Maybe it is not a DB2 at all ?

    Lenny

  7. #7
    Join Date
    Sep 2008
    Posts
    34

    i think it is

    I think it is DB2. The data I am querying resides on an AS/400 V5R3.
    I am using PHP via a Win2K server to access the data via the Client Access ODBC driver.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by curtmorehouse View Post
    I think it is DB2. The data I am querying resides on an AS/400 V5R3.
    I am using PHP via a Win2K server to access the data via the Client Access ODBC driver.
    You can find a lot here:

    http://publib.boulder.ibm.com/infoce...v5r3/index.jsp

    Lenny

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •