News
Abstract
Add SECURE column to QSYS2.SYSCOLUMNS2
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Security Enhancements > Add SECURE column to QSYS2.SYSCOLUMNS2
SYSCOLUMNS2 is a view based on a table function that will return additional information not available in SYSCOLUMNS (such as the allocated length of a varying length column).
Since it is based on a table function, it will typically return results faster if a specific table is specified when querying it.
SELECT * FROM qsys2.syscolumns2
WHERE system_table_schema = ‘MJATST' and system_table_name = ‘T1' ;
The SECURE column externalizes the security setting, if SYSPROC.SET_COLUMN_ATTRIBUTE() has been used to configure the security of the column for database performance analysis tasks.
SECURE column values:
NULL - This column has not been been the target of a call to SYSPROC.SET_COLUMN_ATTRIBUTE.
'0' - This column does not contain data that needs to be secured in a database monitor or plan cache.
'1' - This column contains data that needs to be secured in a database monitor or plan cache.
Example:
call qsys.create_sql_sample('PRODLIB');
CALL SYSPROC.SET_COLUMN_ATTRIBUTE('PRODLIB', 'EMPLOYEE', 'SALARY', 'SECURE YES');
SELECT Column_name,SECURE FROM qsys2.syscolumns2
WHERE system_table_schema = 'PRODLIB' and system_table_name = 'EMPLOYEE' ;
| COLUMN_NAME | SECURE |
| EMPNO | '0' |
| FIRSTNME | '0' |
| MIDINIT | '0' |
| LASTNAME | '0' |
| WORKDEPT | '0' |
| PHONENO | '0' |
| HIREDATE | '0' |
| JOB | '0' |
| EDLEVEL | '0' |
| SEX | '0' |
| BIRTHDATE | '0' |
| SALARY | '0' |
| BONUS | '1' |
| COMM | '0' |
What the database performance analyst will see for this query:
select * from prodlib.employee where salary > 20000;
and
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
21 January 2020
UID
ibm11169770