SQLB-2449: 11.7.3 & 12.0.0 Unload Error

[Clifford Bass]

Hi,

I modify some of the system tables including the SysUserAuth table. When I did an unload from the 11.7.3 database this is what I find near the end:

Code: Select all

ALTER TABLE SYSADM.SYSUSERAUTH ADD UW_SVM_REMARKS CHAR(100)
/
UPDATE SYSADM.SYSUSERAUTH SET 
UW_SVM_REMARKS = 'UW researcher'
 WHERE NAME = 'UWRESRCH'
  AND RESOURCEAUTH = ' '
  AND DBAAUTH = ' '
  AND FLAG = 'R'
  AND EXTUSER = 
/

UPDATE SYSADM.SYSUSERAUTH SET 
UW_SVM_REMARKS = 'Laboratory worker.'
 WHERE NAME = 'LABWORKR'
  AND RESOURCEAUTH = ' '
  AND DBAAUTH = ' '
  AND FLAG = 'R'
  AND EXTUSER = 
/

The load into 12.0.0 of course gives an error: Error: 00915 PRS IVC Invalid constant

Removal of the "AND EXTUSER =" section allows the file to load.

A subsequent unload from the same database now loaded in 12.0.0 shows the same results; so the problem exists also in 12.0.0.

Clifford Bass

[Mike Vandine]

Hi Cliff,

Didn't you report a similar issue with sysuserauth with the 'isrole' column?

You've just *got* to mess around with the system tables, don't you!

Let me have a look...

Best regards,

[Mike Vandine]

Hi Cliff,

I've created SQLB-2449 for this issue and it's assigned to the SB12.0.1 version for a fix.

Do you need a fix for the 11.7 version (he says, hoping the answer of 'No')?

[Clifford Bass]

Hi Mike,

Yep, I just got to mess around with the system tables. Because some are not sufficient in and of themselves. For instance recording additional information about a role such as a textual description. Easier to add it to the system table than to create a separate table.

A long time ago before I realized that capability existed I created separate user information tables which hold names, addresses and some other information. Some of that could have been stored in the system table. After all, it makes sense to include more identification with in a user account record than just their user name, access type and password.

Be that as it may...

Yes, there was a related issue with the IsRole column; but that was exporting properly. It was that the loads of 11.6 and earlier into 11.7 were failing because 11.7 did not recognize that IsRole had morphed into Flag.

You will be happy to hear that no, I don't need a fix for 11.7.

Cliff

[Mike Vandine]

Hi Cliff,

Thanks for the clarification (and the fix version priority).

I understand exactly what you mean. I was just 'having a go' at you, all in good fun.

Cheers!

[Clifford Bass]

Hi Mike,

You are welcome; and no worries!

Have a good rest-of-the-weekend!

Cliff