IDS Bug of the Day: DBINFO

Consider the following query: SELECT dbsname, tabname, DBINFO('dbspace', partnum) FROM systabnames ORDER BY 1, 2;In 12.10.FC13 and earlier, this query works fine. But starting with 12.10.FC14, it fails: 727: Invalid or NULL TBLspace number given to dbinfo(dbspace). Error in line 3Near character position 12 I believe this is because records were added to systabnames in FC14 that have very low partnum values that DBINFO doesn't know what to do with. I have also replicated this in 14.10.FC3. I believe the DBINFO() function needs to be updated to account for these. ------------------------------ TOM GIRSCH ------------------------------ #Informix

Known feature - not a bug according to HCL -with a fix coming that allows environment to override the behaviour But you are correct all scripts/code that use DBinfo are now broke thanks to HCL developers Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Just add a filter to ignore all partnuns < 10240 Art

T32768: DBINFO(DBSPACE) RAISES 727: INVALID OR NULL TBLSPACE NUMBER https://www.ibm.com/support/pages/node/6207160 ​​ ------------------------------ TOM GIRSCH ------------------------------

I did partnum >= 1048576 My support case later suggested the same thing. ------------------------------ TOM GIRSCH ------------------------------

But a PITA because I had several scripts to update and had to re-deploy to about half a dozen systems. ------------------------------ TOM GIRSCH ------------------------------

Yep – HCL broke thousands of scripts

From XC5 you can set DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM to reset back to the original functionaliy An environment variable named DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM was created and can be set to a non-zero value to enable the new feature. It is not active by default to continue the existing behavior . If it is set this way dbinfo('dbspace', partnum) will return NULL instead of an error when an invalid partnum is provided. This is true for any partnum that results in an invalid (db)space number. It includes the pseudo-tables for which the (db)space number is 0. For these tables the partnum is invalid in that sense. If a partnum of NULL is provided, the error -727 will be returned in any case. The environment variable can be set: 1) as an environment variable for the instance 2) as an environment variable for the client like dbaccess 3) as a session environment variable In this order of increasing precedence - meaning session environment takes precedence over client environment which takes precedence over instance environment. You can request a special build - I did Cheers Paul

Hello Paul, Should I request a special build of version 12.10.xc14 to use the DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM environment variable? Setting environment variables doesn't seem to make any difference in behavior. # export DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM=1 # echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmasterDatabase selected. 727: Invalid or NULL TBLspace number given to dbinfo(dbspace). Error in line 1Near character position 56 Database closed. # dbaccess sysmaster -Database selected. > set environment DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM "1";19840: Invalid session environment variable. Error in line 1Near character position 65 > ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

All you can do is ask :-) Classic IBM - take a perfectly good function and tweak it so it breaks everything and then blame us for not using it correctly :-) Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC