Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Fri January 06, 2023 04:37 PM
    Edited by System Admin Fri January 20, 2023 04:37 PM
    Hi all,

    we have IBM CA11.1.7 fetching data from IBM DB2 11.5.

    How do you configure Cognos to filter against columns containing data with german Umlaute like "öäü"?

    We only see "no data" when filtering and logfiles show "?" in SQL statement, instead of the "special char".

    Using excel files and data modules the filters work as expected.

    On ODBC datasources we found that the codepage setting can be done in the drivers ini-file. To make it work.

    Is there something similar in a DB2 connection?


    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------
    #CognosAnalyticswithWatson


  • 2.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Mon January 09, 2023 03:37 AM
    Hi Ralph,

    It seems that IBM DB2 v9.5 is not supported with Cognos Analytics v11.1.7.

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------

    Original Message


  • 3.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Mon January 09, 2023 05:18 AM
    Hi Patrick,

    Checked database version we are using and edited original post to reflect version is 11.5
    dfk1xap03@:/srv/db2/home/fk1xap03 : db2level
    DB21085I This instance or install (instance name, where applicable:
    "fk1xap03") uses "64" bits and DB2 code release "SQL11056" with level
    identifier "0607010F".
    Informational tokens are "DB2 v11.5.6.0", "s2106111000", "DYN2106111000AMD64",
    and Fix Pack "0".
    Product is installed at "/opt/IBM/db2/V11.5.6fp0".

    So ... it's version 11.5. This version is imho supported.

    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------

    Original Message


  • 4.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Mon January 09, 2023 07:46 AM

    I tried two cases. One was to select the column, choose filter, search for ä and filter the column by some of the values which contain ä. Another was to explicitly filter by ä.

    In both cases, the filter executed correctly and the generated SQL contained the German character.

    I do not recall very many problems with non-English characters in our generated SQL and certainly not for a long time.

    I would suggest that you would need to provide a more detailed definition of how the problem manifests itself and you would probably need to contact customer support.

    I am using 11.5.6 too.








    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 5.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Mon January 09, 2023 08:09 AM
    Hi Ralf,

    Thanks for the clarification about the version.

    Do you know the code set (codepage) used by this database? You might want to use:
    db2 get database configuration for database_name.

    The code set value is UTF-8 and the code page value is 1208. It might be an issue with another code set used with this database.

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------

    Original Message


  • 6.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Mon January 09, 2023 11:31 AM

    Locale on App.-Server: 
    root@appserver:/root : locale
    LANG=POSIX
    LC_CTYPE="POSIX"
    LC_NUMERIC="POSIX"
    LC_TIME="POSIX"
    LC_COLLATE="POSIX"
    LC_MONETARY="POSIX"
    LC_MESSAGES="POSIX"
    LC_PAPER="POSIX"
    LC_NAME="POSIX"
    LC_ADDRESS="POSIX"
    LC_TELEPHONE="POSIX"
    LC_MEASUREMENT="POSIX"
    LC_IDENTIFICATION="POSIX"
    LC_ALL=

    DatabaseServer 
    fk1xap03@database:/srv/db2/home/fk1xap03 : db2 get database config

    Database Configuration for Database

    Database configuration release level = 0x1500
    Database release level = 0x1500

    Update to database level pending = NO (0x0)
    Database territory = DE
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 49
    Database collating sequence = IDENTITY
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    Database page size = 4096

    Selecting on database CLI works well 
    fk1xap03@database:/srv/db2/home/fk1xap03 : db2 "select namel0, namel1 from fk1r_account where namel0 like '%Umsatzerlöse%' fetch first 2 rows only"

NAMEL0                                                                           NAMEL1
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
Umsatzerlöse (nur Ges 0030,0036,0397,KITT)                                       Revenues (only comp 0030,0036,0397,KITT)
Summe Umsatzerlöse aus Regierungsgeschäften (nur DFS)                            Total revenue from governmental business (DFS only)

  2 record(s) selected.

    • Extracting SQL from RS shows correct Umlaute
    • SQL logged in cognosserver.log shows "?" where the Umlaut should go
    • The execute SQL in database shows "?"

    So, there is something going wrong in the transformation/creation of the SQL statement on the Application Server.
     
    On the another server running Cognos we are using this locale.  
    sgdem0012172:~ # locale
    LANG=en_US.utf8
    LC_CTYPE="en_US.utf8"
    LC_NUMERIC="en_US.utf8"
    LC_TIME="en_US.utf8"
    LC_COLLATE="en_US.utf8"
    LC_MONETARY="en_US.utf8"
    LC_MESSAGES="en_US.utf8"
    LC_PAPER="en_US.utf8"
    LC_NAME="en_US.utf8"
    LC_ADDRESS="en_US.utf8"
    LC_TELEPHONE="en_US.utf8"
    LC_MEASUREMENT="en_US.utf8"
    LC_IDENTIFICATION="en_US.utf8"
    LC_ALL=en_US.utf8

    I guess, I will try using en_US.utf8 locale and see if that fixes the error.

    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------

    Original Message


  • 7.  RE: Filtering to data with UTF-8 (german umlaute) renders no data

    Posted Tue January 10, 2023 06:59 AM
    Edited by System Admin Fri January 20, 2023 04:38 PM
    Setting the locale fixed it

    On Suse Linux:
    root@appserver : echo LANG="en_US.UTF-8" > /etc/locale.conf
    root@appserver : export LC_CTYPE="en_US.UTF-8"
    • restart Cognos


    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber/
    ------------------------------

    Original Message