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 sysmaster
Database selected.
727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
Error in line 1
Near 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 1
Near character position 65
>
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Original Message:
Sent: Thu June 18, 2020 12:55 PM
From: Paul Watson
Subject: IDS Bug of the Day: DBINFO
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
Original Message:
Sent: 6/18/2020 10:43:00 AM
From: TOM GIRSCH
Subject: RE: IDS Bug of the Day: DBINFO
T32768: DBINFO(DBSPACE) RAISES 727: INVALID OR NULL TBLSPACE NUMBER
https://www.ibm.com/support/pages/node/6207160
------------------------------
TOM GIRSCH
Original Message:
Sent: Wed June 17, 2020 07:23 PM
From: TOM GIRSCH
Subject: 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 3
Near 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