Db2

 View Only
  • 1.  setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    Posted Sun March 05, 2023 08:44 AM
    Edited by Siva Sankaran Sun March 05, 2023 11:53 PM

    In our .net core 3.1 app we are using NuGet Gallery | IBM.Data.DB2.Core 3.1.0.600 package for connecting with DB2 . We are unable to set schema in connection string and this leaves us to use schema name into the query. But it causes security violation issue from our security analysis tool as those query strings are concatenating. Even interpolated string raises the same security violation.

    Either using ConnectionString Builder Object

    DB2ConnectionStringBuilder dB2ConnectionStringBuilder = new DB2ConnectionStringBuilder();             
    dB2ConnectionStringBuilder.Server = "appDB.myorg.com:123";             
    dB2ConnectionStringBuilder.Database = "database";             
    dB2ConnectionStringBuilder.UserID = "user";             
    dB2ConnectionStringBuilder.Password = "***:D***";             
    dB2ConnectionStringBuilder.CurrentSchema = "MySchema";
    string connString = dB2ConnectionStringBuilder.ConnectionString;

    Or using raw connection string

    Server=appDB.myorg.com:1200;Database=database;UID=user;PWD=**:D***;CurrentSchema=MySchema;

    Following code throws IBM.Data.DB2.Core.DB2Exception When connection. Open() invoked. 

    using DB2Connection MyDb2Connection = new DB2Connection(connString); 
    MyDb2Connection.Open();

    The error message is 

    ERROR [57017] [IBM] SQL0332N  Character conversion from the source code page "" to the target code page "" is not supported.  SQLSTATE=57017

    The same issue here https://stackoverflow.com/a/66818035/449413

    Please share a way to set schema dynamically , since we need different schema based on the environment



    ------------------------------
    Siva Sankaran
    ------------------------------



  • 2.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    Posted Mon March 06, 2023 02:27 AM

    Hello Siva,

    You might want to set up a connection procedure on the server side to execute set current schema.

    But the current schema will be set for all connections, and not only connections through your application :

    https://www.ibm.com/docs/en/db2/11.5?topic=parameters-connect-proc-connect-procedure-name-database

    -- 

    Yves-Antoine Emmanuelli



    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------



  • 3.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    Posted Tue March 07, 2023 03:13 AM

    Thanks @Yves-Antoine Emmanuelli . We can't use this solution as multiple applications and team are using the same database server. 



    ------------------------------
    Siva Sankaran
    ------------------------------



  • 4.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    IBM Champion
    Posted Mon March 06, 2023 02:32 AM

    Hi Silva,

    the error message points to a completely different topic. What is the codepage setting on the client side? And what is the codepage setting on the server side?

    Do you have in your code access to a result set structure called SQLCA? This can give you loads of details to the error above.

    please have a look at https://www.ibm.com/docs/en/db2/11.5?topic=tables-sqlca-sql-communications-area

    and SQLDA to get the information of the codepages used in this connection. Either on the client side or server side, there might be a codepage conversion table missing, leading to this error.

    Cheers



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------



  • 5.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    Posted Tue March 07, 2023 03:12 AM
    Edited by Siva Sankaran Tue March 07, 2023 03:14 AM

    Thanks @Roland Schock  . I will check those questions with our DBA & team.  And I will try to get SQLCA structure in our .net app. 



    ------------------------------
    Siva Sankaran
    ------------------------------



  • 6.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    Posted Tue March 07, 2023 03:15 AM

    Any .net expert here  Please share details about how to get SQLCA result structure in c#.net . I am using NuGet Gallery | IBM.Data.DB2.Core 3.1.0.600.  As @Roland Schock  points out this may give more info to troubleshoot this issue



    ------------------------------
    Siva Sankaran
    ------------------------------



  • 7.  RE: setting currentSchema in connection string causes IBM.Data.DB2.Core.DB2Exception

    IBM Champion
    Posted Tue March 07, 2023 04:18 AM
    Edited by Roland Schock Tue March 07, 2023 04:18 AM

    Hi Siva,

    as I kicked this off... ;-)

    First of all, I'm neither a .net expert nor a developer (I was once in a long distant past).

    Looking at the class library you are using, there is no means in the object construction to feed back the error in an SQLCA structure. So probably the underlying class library throws an exception. The default exception handler catches this exception object and is giving you the error message you finally see.

    To get more details, you'll probably have to develop your own exception handler to catch the Db2 InfoMessageEvent (see https://www.ibm.com/docs/en/db2/11.5?topic=events-infomessage). As far as I have flicked through the docs, this will give you an ErrorCollection object, which you can analyze. https://www.ibm.com/docs/en/db2/11.5?topic=properties-item

    A completely different option could be to setup a .NET trace https://www.ibm.com/docs/en/db2/11.5?topic=net-tracing-data-server-provider and analyze the dump there.

    Cheers



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------