Db2

 View Only
  • 1.  DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    Posted Wed May 17, 2023 04:59 PM

    Hello Team,

    We have migrated our Data Services from Oracle to DB2 environment.  We have also exported source repositories to target system repositories.  But in target environment, when we try to execute the jobs, they are failing.  We found in the log that DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue. For example, in a Template table there is a field "Global Account Number" in a Template table Customer_Master.  During transformation while loading data through the job, DB is converting statement as INSERT INTO CUSTOMER_MASTER("GLOBAL ACCOUNT NUMBER") VALUES('ACCD') and throwing error as field "GLOBAL ACCOUNT NUMBER" not found.   It is not treating column names as case in-sensitive.     In Source environment, this works fine.

    Could you please let us know how to resolve this issue



    ------------------------------
    Yaseen Shaik
    ------------------------------


  • 2.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Wed May 17, 2023 08:05 PM
    Edited by Jan Nelken Thu May 18, 2023 07:54 AM

    If you created table with column name "Global Account Number" - you have to refer to it with the exact name as in "Global Account Number". 

    Your SQL statement is not converted to upper case by Db2; you are to refer to columns named explicitly in mixed case with exact name as in "Global Account Number".

    Personally I am convinced that using embedded spaces and mixed case in naming Db2 objects is a bad idea though.



    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Thu May 18, 2023 02:17 PM

    DB2 converts all object names (tables, columns, indexes, ...) to UPPERCASE when it stores them in the system catalogue, unless you force the mixed case (but why would you want to do that?).
    Check this blog for more info:
        https://datageek.blog/en/2014/04/15/db2-basics-capitalization/
    HTH

    Cheers, Damir




    ------------------------------
    Damir Wilder
    ------------------------------



  • 4.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Thu May 18, 2023 02:27 PM

    True - Db2 converts ALL OBJECT NAMES to uppercase when writing them to system catalogue - I just stated that contrary to what original poster wrote, 

    SQL statement is not converted to upper case by Db2.



    ------------------------------
    Jan Nelken
    ------------------------------



  • 5.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Fri May 19, 2023 04:26 AM

    Hi Yaseen,
    The quickest option to work around this issue (that I can think of right now) would be to create an upper-case alias for each mixed-case object name (tables/views) found in the system catalogue, for example:
        CREATE ALIAS TABSCHEMA.TABLENAME FOR "TabSchema.TabName"
    That way you won't have to alter any of your SQL statements as they will be able to reach the tables via their aliases.

    Regards, Damir



    ------------------------------
    Damir Wilder
    ------------------------------



  • 6.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    Posted Mon May 22, 2023 07:50 AM
    We are fine with Table names, but the issue is with Mixed case columns of tables , so any suggestion for this?

    Regards
    Yaseen






  • 7.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Mon May 22, 2023 10:08 AM
    Edited by Jan Nelken Mon May 22, 2023 10:57 AM
    ALTER TABLE <tabname> RENAME COLUMN <column_name> TO <new_column_name>

    A simple example:

    db2 create table MyTable (\"BadName\" Char(1))
    DB20000I  The SQL command completed successfully.

    db2 describe table mytable                               Data type                     Column

    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    BadName                         SYSIBM    CHARACTER                    1     0 Yes

      1 record(s) selected.

    db2 alter table MYTABLE rename column \"BadName\" to BADNAME
    DB20000I  The SQL command completed successfully.

    db2 describe table mytable

                                  Data type                     Column

    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    BADNAME                         SYSIBM    CHARACTER                    1     0 Yes

    1 record(s) selected.

    ------------------------------
    Jan Nelken
    ------------------------------



  • 8.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    Posted Mon May 22, 2023 11:24 AM
     Actual issue is more complex than the above solution  and we don't want that statement to be changed to upper case.
    • we don't want that statement to be changed to upper case 

    • we don't want that statement to be changed to upper case 

    • we don't want that statement to be changed to upper case 

    • we don't want that statement to be changed to upper case 







  • 9.  RE: DB2 client is changing the correponding SQL Statements to upper case, which is causing the issue

    IBM Champion
    Posted Mon May 22, 2023 12:44 PM
    Edited by Jan Nelken Tue May 23, 2023 08:40 AM

    Would you be so kind and SHOW what statements you don't want to change to uppercase?

    You have column names defined in the table - and in system catalogue - in MiXeD cAsE.

    You have to refer to them in your application code in mixed case as well. You need to escape quotes in column name so they will be passed to Db2 unchanged.

    In my CLP (Command Line Processor) example I used escape quotes to pass to Db2 mixed case column name:

    db2 create table MyTable (\"BadName\" Char(1))

    You may want to explain what driver and which version you are using in what application written in what language - you will need to provide some evidence (CLI trace, ODBC trace, JDBC trace) so culprit of folding statement into upper case can be found.



    ------------------------------
    Jan Nelken
    ------------------------------