IBM Support

IC69143: STORED PROCEDURE PARAMETERS CORRUPTED DURING PREPARE USING EXECUTEREADER METHOD OF ODBC INTERFACE AND C# PROGRAM

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Problem Abstract:
    The parameters of a stored procedure call get corrupted during
    the prepare phase when using the ODBC Interface to execute the
    SQL with the ExecuteReader method.
    
    Client: Windows 2003 64 Bit, DB2 V9.1 FP6a 64 Bit
    Server: remote zOS based DB2 8.1.5 database stored procedure
    
    How easy is problem to reproduce? Easy
    
    Problem Desc:
    The ExecuteReader() method will produce unexpected results
    depending on how the stored procedure uses the corrupted stored
    procedure parameters.
    
    Here is a c# program that uses ODBC interface that can reproduce
    this defect:
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data.Odbc;
    using System.Data.OleDb;
    
    namespace testDB
    {
        class Program
        {
            static void Main(string[] args)
            {
                string cmdstr = "";
                if (args.Length > 0)
                {
                    cmdstr = args[0];
                }
                TestODBCDB2(cmdstr);
            }
    
            static void TestODBCDB2(String cmdstr)
            {
                // create and open a new connection
                try
                {
                    // open the connection
                    Console.WriteLine("Openning connection....");
                    String connStr =
    "DSN=SAMPLE;UID=UserID;PWD=password;DATABASE=SAMPLE;WSID=serv
    erName";
                    OdbcConnection Conn = new
                    OdbcConnection(connStr);
                    Conn.Open();
                    OdbcCommand command = new OdbcCommand("call
    TEST_STORED_PROC ('Param1',0);");
                    command.Connection = Conn;
                    OdbcDataReader reader =
    command.ExecuteReader(System.Data.CommandBehavior.SequentialAcce
    ss);
                    Console.WriteLine("printing result");
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Console.Write(reader.GetValue(i));
                            Console.Write("\t");
                        }
                        Console.WriteLine("");
                    }
                    command.Dispose();
                    Console.WriteLine("done");
                }
    
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    Console.WriteLine(e.StackTrace);
                }
            }
        }
    }
    

Local fix

  • Do not use the Executereader method.  Prepare and execute the
    SQL statement that calls a stored procedure separately.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Problem Abstract:                                            *
    *                                                              *
    * The parameters of a stored procedure call get corrupted      *
    * during the prepare phase when using the ODBC Interface       *
    * to execute the SQL with the ExecuteReader method.            *
    *                                                              *
    * Client: Windows 2003 64 Bit, DB2 V9.1 FP6a 64 Bit            *
    * Server: remote zOS based DB2 8.1.5 database stored procedure *
    *                                                              *
    * How easy is problem to reproduce? Easy                       *
    *                                                              *
    * Problem Desc:                                                *
    * The ExecuteReader() method will produce unexpected results   *
    * depending on how the stored procedure uses the corrupted     *
    * stored procedure parameters.                                 *
    *                                                              *
    * Here is a c# program that uses ODBC interface that can       *
    * reproduce this defect:                                       *
    * <BEGIN C# program>                                           *
    * using System;                                                *
    * using System.Collections.Generic;                            *
    * using System.Text;                                           *
    * using System.Data.Odbc;                                      *
    * using System.Data.OleDb;                                     *
    *                                                              *
    * namespace testDB                                             *
    * {                                                            *
    * class Program                                                *
    * {                                                            *
    * static void Main(string[] args)                              *
    * {                                                            *
    * string cmdstr = "";                                          *
    * if (args.Length > 0)                                         *
    * {                                                            *
    * cmdstr = args[0];                                            *
    * }                                                            *
    * TestODBCDB2(cmdstr);                                         *
    * }                                                            *
    *                                                              *
    * static void TestODBCDB2(String cmdstr)                       *
    * {                                                            *
    * // create and open a new connection                          *
    * try                                                          *
    * {                                                            *
    * // open the connection                                       *
    * Console.WriteLine("Openning                                  *
    * connection....");                                            *
    * String connStr =                                             *
    * "DSN=SAMPLE;UID=UserID;PWD=password;DATABASE=SAMPLE;WSID=serve
    * OdbcConnection Conn = new                                    *
    * OdbcConnection(connStr);                                     *
    * Conn.Open();                                                 *
    * OdbcCommand command = new OdbcCommand("call                  *
    * TEST_STORED_PROC ('Param1',0);");                            *
    * command.Connection = Conn;                                   *
    * OdbcDataReader reader =                                      *
    *                                                              *
    * command.ExecuteReader(System.Data.CommandBehavior.SequentialAc
    * Console.WriteLine("printing result");                        *
    * while (reader.Read())                                        *
    * {                                                            *
    * for (int i = 0; i <                                          *
    * reader.FieldCount;i++)                                       *
    * {                                                            *
    * Console.Write(reader.GetValue(i));                           *
    * Console.Write("\t");                                         *
    * }                                                            *
    * Console.WriteLine("");                                       *
    * }                                                            *
    * command.Dispose();                                           *
    * Console.WriteLine("done");                                   *
    * }                                                            *
    * catch (Exception e)                                          *
    * {                                                            *
    * Console.WriteLine(e.ToString());                             *
    * Console.WriteLine(e.StackTrace);                             *
    * }                                                            *
    * }                                                            *
    * }                                                            *
    * }                                                            *
    * <END C# program>                                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.1 FP10 or use the circumvention given      *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.1 Fix Pack 10
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC69143

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-10

  • Closed date

    2011-06-29

  • Last modified date

    2011-06-29

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC69553 IC69554 IC71175

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R910 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2011