Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: DB2 Column names!

    Hi,
    I'm trying to tag field values with the field names after extracting records from a DB2 table. Does anyone know how to extract the column names along with the column values from the table - is it possible using a function or a statement?

    eg.
    Customer (Name of table)
    Name Age Sex Location
    Don 28 M US
    Jane 21 F UK

    I have to extract each customer record and tag the values as follows:
    <Name>Don<\Name><Age>28<\Age><Sex>M<\Sex><Location >US<\Location>
    <Name>Jane<\Name><Age>21<\Age><Sex>F<\Sex><Locatio n>UK<\Location>

    Would appreciate anyone's help with this.

    Thanks,
    Maxdp
    *****

  2. #2
    Join Date
    Nov 2003
    Posts
    14
    with java?????


    If u use java,jdbc,



    like this


    System.out.println(sqlSelect);
    rs=db.execQuery(sqlSelect);
    rsmd=rs.getMetaData();
    col_len=rsmd.getColumnCount();
    }
    catch(SQLException e)
    {
    System.out.println("˴");
    }
    String col_name[]=new String[col_len];
    int col_type[]=new int[col_len];
    for(int i=0;i<col_len;i++)
    {
    try {
    col_name[i]=rsmd.getColumnName(i+1);
    col_type[i]=rsmd.getColumnType(i+1);
    // System.out.println(col_name[i]+"type"+col_type[i]);
    } catch (SQLException e) {
    System.out.println("˴");
    }
    }
    try {
    while(rs.next())
    {

    for(int j=0;j<col_len;j++)
    {
    if(col_type[j]!=93)
    {
    hashtable.put(col_name[j],(rs.getString(j+1)==null)?"":rs.getString(j+1));
    //System.out.println(col_name[j]+"name "+rs.getString(j+1));
    }
    }
    }
    } catch (SQLException e) {
    }
    db.close();
    return hashtable;
    }

  3. #3
    Join Date
    Dec 2003
    Posts
    11

    Re: DB2 Column names!

    Thanks Richard. I forgot to say that I'm trying to do this by calling a COBOL sub-routine on the mainframe through a triggered stored procedure. Hope someone can provide some info on this, without me having to actually include a file layout for the table columns.
    Maxdp
    *****

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: DB2 Column names!

    Not sure this is going down the wrong path. Within a stored procedure you can have a SELECT which builds the tags around the data values and produce a result set.

    SELECT '<Name>' || name || '</Name><Age>' || age || ......
    FROM table
    WHERE ....

    You can also use system tables to build a select statement dynamically to pull the columns and data. I'm familiar with doing that within UDB but not sure about the mainframe.

    Originally posted by maxdp
    Hi,
    I'm trying to tag field values with the field names after extracting records from a DB2 table. Does anyone know how to extract the column names along with the column values from the table - is it possible using a function or a statement?

    eg.
    Customer (Name of table)
    Name Age Sex Location
    Don 28 M US
    Jane 21 F UK

    I have to extract each customer record and tag the values as follows:
    <Name>Don<\Name><Age>28<\Age><Sex>M<\Sex><Location >US<\Location>
    <Name>Jane<\Name><Age>21<\Age><Sex>F<\Sex><Locatio n>UK<\Location>

    Would appreciate anyone's help with this.

    Thanks,
    Maxdp
    *****

  5. #5
    Join Date
    Dec 2003
    Posts
    11
    hey dmmac, thanks for the creative suggestion, but I think that performance gets affected too much doing it that way. Actually tested it and found that for tagging 7 fields of around 3200 records, the difference between the non-tagged retrieve and the tagged one was like 5 secs - 1.50 and 6.93 respy. How do u do it on the UDB and how's the performance on that?

  6. #6
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by maxdp
    hey dmmac, thanks for the creative suggestion, but I think that performance gets affected too much doing it that way. Actually tested it and found that for tagging 7 fields of around 3200 records, the difference between the non-tagged retrieve and the tagged one was like 5 secs - 1.50 and 6.93 respy. How do u do it on the UDB and how's the performance on that?
    I'm not sure if it is supported from within a stored procedure but you can get the column names from the SQLDA after preparing the SQL statement. I'm not at the office until next week so I can't show an example right now.

    I do have my doubts wether this will perform any faster then the concat suggestion from dmmac

Posting Permissions

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