Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

JSON_OBJECT returning SQL0171N error when it shouldn't

  • 1.  JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Tue March 12, 2024 06:53 PM

    db2 11.5 on windows,  JSON_OBJECT fails to execute with SQL0171N no matter what. 

    db2 "select json_object (key 'id' value 1) from sysibm.sysdummy1"
SQL0171N  The statement was not processed because the data type, length or
value of the argument for the parameter in position "1" of routine
"SYSIBM.JSON_OBJECT" is incorrect. Parameter name: "".  SQLSTATE=42815

    we first notice id trying to create a json_object from a decimal column. but no matter what we try, it fails out with the same error. 

    Even running the sample code from the doc page
    JSON_OBJECT scalar function - IBM Documentation

    db2 "VALUES JSON_OBJECT(KEY 'first' VALUE 'John', KEY 'last' VALUE 'Doe')"
SQL0171N  The statement was not processed because the data type, length or
value of the argument for the parameter in position "1" of routine
"SYSIBM.JSON_OBJECT" is incorrect. Parameter name: "".  SQLSTATE=42815

    This db is currently at 11.5 release,  but this db was not originally created under this version. it is an old db that has been upgraded several times..  10.x for sure. 

    If we create a sample db,  and try the same everything works.   We need assistance to fix this issue.  

    this is our current level 

    db2level
DB21085I  This instance or install (instance name, where applicable: "DB2")
uses "64" bits and DB2 code release "SQL11058" with level identifier
"0609010F".
Informational tokens are "DB2 v11.5.8000.317", "s2209201700",
"DYN2209201700WIN64", and Fix Pack "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

    Appreciate any kind of support, specially from IBM as this seems to be something wrong with json functions uppon db migrations.  

    IBM please , help ! 



    ------------------------------
    Samuel Pizarro
    ------------------------------


  • 2.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Wed March 13, 2024 03:32 AM
    Edited by Jan Nelken Wed March 13, 2024 03:32 AM

    After migration to Db2 V11.5 - you did run db2updt115, right?



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

    Original Message


  • 3.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Wed March 13, 2024 08:20 AM

    I don't know exactly "when"  the issue started..  this is the 1st time we are trying to use JSON functionality on this old db. 

    It could have happen at any previous migration path..  10.1 to 10.5, 10.5 to 11.1,  etc ...  

    But yes,  we have executed the corresponding db2updvXXX  on each of them.   

    Just in case, I've just re-executed it , the db2updv115 returns ok, completed successfully.  but the json_object issue/error is still happening. 

    Nothing gets fixed. 

    Regards



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message


  • 4.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Wed March 13, 2024 11:32 AM

    And one more question:

    at each stage (major version change, minor version) you did run db2 migrate database xxx?

    How big is this database?
    Is it feasible to create a test database and reload data (HPU unload -> LOAD) or EXPORT -> LOAD?



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

    Original Message


  • 5.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Wed March 13, 2024 08:35 PM
    Edited by Samuel Pizarro Wed March 13, 2024 08:35 PM

    yes..   the db was for sure properly migrated..  otherwise you can't really run the db without it.. it's mandatory. 

    DB is about 2TB.  I don't have HPU unload, it's not a built-in feature..  

    Digging further,  this db was so old that it was not created as UNICODE/UTF-8 db.    I believe this might be the issue.  I am not finding the proper pre-requirements for json functionality..  but I vaguely remember something about this..  but I am really not sure. 

    As a mater of fact,  if unicode is mandatory, there should be a better error than this..  if the pre-req is not met.. and someone tries to use the functions. 

    That's a terrible error message if this is really the case. 

    Can someone from ibm confirm this ? I was really not able to find this statement in ibm db2 docs webpages. 

    And if that is true,  I guess I will really have to recreate db from scratch and re-laod it !!! 

    OUCH!!  IBM should really provide a better way of changing the database code-page/collation on the fly.  Several other DBMS offers that..  

    Regards



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message


  • 6.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 01:54 AM
    Edited by Roland Schock Thu March 14, 2024 01:54 AM

    Hi Samuel,

    for a history of upgrades and migrations you can query a system table: SELECT * FROM SYSIBM.SYSVERSIONS
    It would be great, if you can share a screenshot of the output here.

    Then, AFAIK JSON did not require UTF-8min the beginning, but with Db2 11.1.4.4 some new and faster built-in JSON functions were published. These require a database to use UTF-8 codeset. See here https://www.ibm.com/docs/en/db2/11.5?topic=json-sql-access-documents

    And BTW, you cannot change a codepage of a database easily on the fly! Each character symbol which goes into the database and gets stored eventually on disk is assigned to a codepoint and a serialization pattern. Changing the database codepage means, all data has to be re-coded from old codepoints to new codepoints and the new serialization pattern. This is a so rare use case, none of the database manufacturers I know have developed functions for such a conversion. 
    Hence only EXPORT/LOAD are your tools for changing the codepage (well, of course, replicating the data to a new database also does the trick).

    And BTW you can get rid of some other old ballast of previous versions and old disk formats! Is your system catalog already in Automatic Storage? 
    I'm looking forward to your results of select * from sysibm.sysversions

    Cheers

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

    Original Message


  • 7.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 08:32 AM

    Hi Roland.. Thanks for the feedback 

    here is the sysversions output 

    db2  "select * from sysibm.sysversions"

VERSIONNUMBER VERSION_TIMESTAMP          AUTHID       VERSIONBUILDLEVEL
------------- -------------------------- ------------ ------------------------------
     10050500 2015-12-02-23.21.03.370000 DB2ADMIN     s141128
     11010404 2019-08-07-01.38.30.848001 SYSIBM       s1811091400
     11050800 2022-11-13-00.35.18.982001 SYSIBM       s2209201700

  3 record(s) selected.

    was born in 10.5 .  and yes it was created with AUTOMATIC STORAGE. so, we are good in that sense.. 

    Ok, it seems that we have no other option..   RE-CREATE / RELOAD will be..  

    Thanks 



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message


  • 8.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 04:49 AM

    Samuel, 

    As indicated by Roland - since Db2 V11.1.4 - JSON related build-in function do require UTF-8 database:


    The default code set for a database is UTF-8.

    USING CODESET codeset
    Specifies the code set to be used for data entered into this database. After you create the database, you cannot change the specified code set.

    TERRITORY     territorySpecifies the territory identifier or locale identifier to be used for data entered into this database. After you create the database, you cannot change the specified territory. The combination of the code set and territory or locale values must be valid.

    Your only option is to reload database. 
    Strategy could be to create a new database, then LOAD from cursor from old database. You may also want to consider obtaining HPU license for the migration phase.
    How active is your database? How long you can afford to keep old database offline?

    May I suggest to pay attention at the planning phase of future migration(s) to warning and notes like this (referring to AUTOMATIC STORAGE parameter values):



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

    Original Message


  • 9.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 08:37 AM

    Hi Jan

    Ok,  thanks for the confirmation about utf-8 requirements..   so based on this.  don't you agree that those functions should return a better error message instead of this unexpected one, which is pretty hard to guess what's happening ? 

    I hope someone from IBM agrees with that.  

    --------

    About the reload..  the database is very active..  but the customer will have to choose..  if they want to start using json built-in stuff, they will have the offer an offline window.   We will start doing some tests..  to best estimate the total required time to re-load this.  

    Appreciate everyone's assistance on this.  

    Regards



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message


  • 10.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 11:54 AM

    Samuel - I fully agree with you wrt error message - we all are facing "challenging" error pessages; may I suggest to open an AHA request.

    On the other hand - if I was tasked in more defensing JSON function call coding - I would have to evaluate performance impact of addition like this:

    <code
    if database is not UTF-8
      then
         raise exception
       else
         proceed;
    /code>

    in any invocation of JSON internal function.

    As for reloading data i you don't have HPU then load from cursor is probably your best option. Let me know whether you may want any additional support - feel free to contact me at: jan.m.nelken@gmail.com and we can work something out.



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

    Original Message


  • 11.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Fri March 15, 2024 08:31 AM

    Hi Jan

    Well.. it seems they already have such code implemented in some places..   but it seems on not all of them...  So I really thing this is a bug,  and not a case for an AHA idea. 

    I tried to run the CAST approach suggested by roland,  and here is what happened.. 

    db2 "select json_object (key unicode_str('id') value 1 returning varbinary(40) format json encoding utf8) from sysibm.sysdummy1"
SQL1233N  The statement failed because the statement includes the use of
functionality that is supported only in Unicode databases or Unicode databases
with specific database collations, but the current database is not a Unicode
database or is a Unicode database with unsupported database collations.
SQLSTATE=560AA

    This time I get a proper error explaining what's happening..  

    In my humble opinion, this should have been the error to be reported in the first case as well. 

    Regards and again,  THANK YOU very much for your assistance.  



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message


  • 12.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Thu March 14, 2024 01:50 PM
      |   view attached

    Hi Samuel,

    I was pondering this afternoon about your issue and searched a bit my old documentation folder.

    In 2018 IBM announced Db2 11.1.4.4. @George Baklarz presented at IDUG in Malta about the new changes for JSON (On the IDUG website his presentation would be free to download, but currently this is not so easy. so I attach it here).

    If you look at pages 3..5 you can see, the state of the JSON functions at that time. IBM implemented something and then the standard got finalized and IBM adopted the new standard then. The older functions in SYSTOOLS schema are still available. But probably the new functions got signatures for the parameters to accept UTF-8 only (=this is my educated guess here).

    So an idea is to cast the strings you are using with the newer functions accordingly. I cannot try it myself, as I would have to setup an instance with such an old code first. In the example below, you can see also some additional options I have found in the docs. Either UNICODE_STR() works or probably also CAST('Last name' AS VARCHAR(30) CODEUNITS32).

    The following example was running without error on my Ubuntu test system:

    db2 "SELECT JSON_OBJECT(KEY UNICODE_STR('Last name') VALUE LASTNAME returning varbinary(40) format json encoding utf8)  FROM EMPLOYEE WHERE EMPNO = '000020'"

    I could reproduce the error message, when I used invalid combinations, like

    db2 "SELECT JSON_OBJECT(KEY UNICODE_STR('Last name') VALUE LASTNAME returning varchar(40) format bson)  FROM EMPLOYEE WHERE EMPNO = '000020'"

    as the binary BSON format cannot be converted to VARCHAR. It must be a binary format with BSON. I got the same error:

    SQL0171N  The statement was not processed because the data type, length or 
    value of the argument for the parameter in position "3" of routine 
    "SYSIBM.JSON_OBJECT" is incorrect. Parameter name: "".  SQLSTATE=42815

    It is a generic error for the incompatible parameters in these functions.

    Maybe casting can help you to extend the time before you have to convert the database to the new format with UTF-8.

    Cheers



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

    Attachment(s)

    pdf
    EU18E07.pdf   13.19 MB 1 version
    Original Message


  • 13.  RE: JSON_OBJECT returning SQL0171N error when it shouldn't

    Posted Fri March 15, 2024 08:32 AM

    Hi Roland

    I've tried you approach..   but it seems I can't use this casting approach in a non-unicode db..  

    db2 "select json_object (key unicode_str('id') value 1 returning varbinary(40) format json encoding utf8) from sysibm.sysdummy1"
SQL1233N  The statement failed because the statement includes the use of
functionality that is supported only in Unicode databases or Unicode databases
with specific database collations, but the current database is not a Unicode
database or is a Unicode database with unsupported database collations.
SQLSTATE=560AA

    Anyway,  appreciate your effort on this.  

    Regards 



    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message