IBM Support

DB2 select script (run inside TI process) does not show all expected values (missing blank/null value entries), caused by incorrect SQL syntax relating to DB2 script 'SELECT' (run in Turbo Integrator TI script)

Troubleshooting


Problem

Customer wants to read some information from the 'main' Controller database, via Planning Analytics (TM1).
  • For example, they want to read from the table XDB19
To achieve this, they read the data via a DB2 script, running inside TM1 Turbo Integrator (TI)
However, the results do not include all expected rows
  • Specifically, the script cannot find rows where the select answers are set to blank / empty / null

Symptom

Example:
Imagine a scenario where the customer runs a script similar to:
SELECT SUBSTR(perakt,1,4), bol, vkod, konto, belopp, motbol, dim2, btyp FROM XDB19
WHERE SUBSTR(perakt, 5,2) = 'AC'
AND SUBSTR(perakt,1,4) = '1907' AND ktypkonc <> 'LE'
AND ktypkonc <> 'AB'
AND (btyp = ''  OR btyp = 'BC' OR btyp = 'CD')

The idea of the script is that it wants to show all the values where:
  • Actuality = AC
  • Period = 1907
  • Group (relating to elimination) is *not* 'LE' or 'AB'
  • Journal type is either:
    • Blank / empty / null
    • or BC
    • or CD
If you run that script, then the TI gives a preview of the first 10 results. None of the rows have btype = ''

This is not what the customer is expecting. Specifically, inside the database table, there are lots of rows where the above is satisfied, where btype = ''.

Document Location

Worldwide

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSMRTZ","label":"IBM Cognos Controller on Cloud"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3.1","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
20 May 2025

UID

ibm10997710

Page Feedback