Db2 operations executed by the DBA user in DevOps Experience

Edit online

DevOps experiences can perform various Db2 operations by using the DBA user ID defined in configuration YAML files.

Notes:
  • The DBA userid will always be used for subsystem discovery. Therefore, it requires the permissions listed for the 'Db2 subsystem discovery' row.
  • If the DBA userid is overridden in the Subsystem registration page, the subsystem-specific DBA user is used.
  • If the Register using my user id toggle is set to True for subsystem registration, the logged-in user requires permissions listed in the 'Register a Db2 subsystem' row.
Table 1. Db2 operations used by DevOps experiences
Function Primary authentication ID used for operation Operation Authorization
Db2 Subsystem discovery DBA userid 
SELECT <…> FROM 
SYSIBM.SYSPLAN
SYSIBM.SYSPACKAGE
SYSIBM.SYSTABLES
SYSIBM.SYSUSERAUTH
SYSIBM.SYSDATABASE
SYSIBM.SYSTABLESPACE
SYSIBM.SYSVIEWDEP
SYSIBM.SYSSYNONYMS
SYSIBM.SYSTRIGGERS
SYSACCEL.SYSACCELERATORS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSINDEXES
SYSIBM.SYSPACKLIST
SYSIBM.SYSRELS
SYSIBM.SYSROUTINES
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSSEQUENCES
SYSIBM.SYSPLANDEP
 SELECT catalog tables from the list.
For details, refer to
Register a Db2 subsystem (always runs along with Db2 subsystem discovery) DBA userid OR logged in user 
SELECT <…> FROM SYSIBM.SYSLEVELUPDATES
 SELECT the SYSIBM.SYSLEVELUPDATES. 
SET CURRENT SQLID = <dbaSqlid>
 For details, refer to SET CURRENT SQLID statement. 
SET CURRENT SCHEMA=<subsystem Db2 object schema>
 For details, refer to SET SCHEMA statement. 
CREATE DATABASE
 For details, refer to CREATE DATABASE statement. 
CREATE TABLESPACE
 For details, refer to CREATE TABLESPACE statement. 
CREATE TABLE
 For details, refer to CREATE TABLE statement. 
CREATE INDEX
 For details, refer to CREATE INDEX statement. 
ALTER TABLE
(previously created)		 Ownership of the table. 
GRANT DELETE, SELECT, INSERT, UPDATE ON TABLE <TBNAME> TO PUBLIC
(previously created table)		 Ownership of the table. 
CREATE VIEW
(on previously created table)		 For details, refer to CREATE VIEW statement. 
INSERT INTO
(previously created table)		 Ownership of the table.
Register a Db2 subsystem (always runs along with Db2 subsystem discovery) DBA userid OR logged in user 
CREATE LOB TABLESPACE
 For details, refer to CREATE LOB TABLESPACE. 
CREATE AUXILIARY TABLE
 For details, refer to CREATE AUXILIARY TABLE statement. 
CREATE VARIABLE
 For details, refer to CREATE VARIABLE statement. 
GRANT SELECT ON TABLE <TBNAME> TO PUBLIC
(on previously created table)		 Ownership of the table. 
GRANT ALL ON VARIABLE <VARNAME> TO PUBLIC
(on previously created variable)		 Ownership of the table. 
CREATE GLOBAL TEMPORARY TABLE
 For details, refer to CREATE GLOBAL TEMPORARY TABLE statement. 
CREATE PROCEDURE
 For details, refer to CREATE PROCEDURE statement (external procedure). 
GRANT EXECUTE ON PROCEDURE <PROCNAME> TO PUBLIC
 Ownership of the procedure.

For details, refer to GRANT statement (function or procedure privileges).

 
GRANT EXECUTE ON PLAN <PLANNAME> TO PUBLIC
 For details, refer to GRANT statement (plan privileges). 
BIND PACKAGE
 For details, refer to BIND PACKAGE subcommand (DSN). 
BIND PLAN
 For details, refer to BIND PLAN subcommand (DSN).
Remove Db2 subsystem DBA userid 
SET CURRENT SCHEMA
 For details, refer to SET SCHEMA statement. 
SET CURRENT SQLID = <dbaSqlid>
 For details, refer to SET CURRENT SQLID statement. 
ALTER TABLE <TBNAME> DROP CONSTRAINT
(table created in register Db2 subsystem step)		 Ownership of the table.

For details, refer to ALTER TABLE statement.

 
DROP TABLE
(table created in register Db2 subsystem step)		 Ownership of the procedure.

For details, refer to DROP statement.

 
DROP TABLESPACE
(TS created in register Db2 subsystem step)		 Ownership of the procedure.

For details, refer to DROP statement.

 
DROP DATABASE
(DB created in register Db2 subsystem step)		 Ownership of the procedure.

For details, refer to DROP statement.

 
REVOKE EXECUTE ON PROCEDURE
(procedure created in register Db2 subsystem step)		 Ownership of the procedure. 
DROP PROCEDURE
 Ownership of the procedure.

For details, refer to DROP statement.

 
FREE PACKAGE
 For details, refer to FREE PACKAGE subcommand (DSN).
Register an application DBA userid 
SELECT <…> FROM
SYSIBM.SYSTABLES
SYSIBM.SYSDATABASE
SYSIBM.SYSINDEXES
SYSIBM.SYSTRIGGERS
SYSIBM.SYSROUTINES
SYSIBM.SYSVIEWS
SYSIBM.SYSINDEXES
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSTABLESPACE
SYSIBM.SYSAUXRELS
SYSIBM.SYSVIEWDEP
SYSIBM.SYSCOLUMNS
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSRELS
SYSIBM.SYSSYNONYMS
 SELECT catalog tables from the list.
For details, refer to
Provision an instance, Create snapshot, Restore snapshot DBA userid 
SELECT <…> FROM
SYSIBM.SYSTABLESPACE
SYSIBM.SYSDATABASE
SYSIBM.SYSTABLES
SYSIBM.SYSINDEXES
SYSIBM.SYSROUTINES
SYSIBM.SYSAUXRELS
SYSIBM.SYSRELS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSTRIGGERS
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSVIEWS
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSCHEMAAUTH
 SELECT catalog tables from the list.
For details, refer to 
SET CURRENT SQLID = <dbaSqlid>
 For details, refer to SET CURRENT SQLID statement. 
CREATE <object>
 For details, refer to CREATE DATABASE statement. 
GRANT BINDADD
 For details, refer to GRANT statement. 
GRANT CREATEALIAS
 For details, refer to GRANT statement. 
GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA
 For details, refer to GRANT statement. 
GRANT CREATE ON COLLECTION
 For details, refer to GRANT statement. 
GRANT DBADM ON DATABASE
(privileges on DB created above to members of a team)		 Ownership of the database. 
SET CURRENT SCHEMA
 For details, refer to SET SCHEMA statement. 
UNLOAD/LOAD
 Migrate data for provisioned objects.
Deprovision an instance DBA userid 
SELECT <…> FROM
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSRESAUTH
 SELECT catalog tables from the list.
For details, refer to 
DROP <object>
 For details, refer to DROP statement.
Add/remove user to a team, which has provisioned instance DBA userid 
SELECT <…> FROM
SYSIBM.SYSROUTINES
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSRESAUTH
SYSIBM.SYSSCHEMAAUTH
 SELECT catalog tables from the list.
For details, refer to 
SET CURRENT SQLID = <dbaSqlid>
 For details, refer to SET CURRENT SQLID statement. 
GRANT BINDADD
 For details, refer to GRANT statement. 
GRANT CREATEALIAS
 For details, refer to GRANT statement. 
GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA
 For details, refer to GRANT statement. 
GRANT CREATE ON COLLECTION
 For details, refer to GRANT statement. 
GRANT DBADM ON DATABASE
 For details, refer to GRANT statement. 
REVOKE CREATEIN, ALTERIN, DROPIN ON SCHEMA
 For details, refer to Revoking privileges with the REVOKE statement. 
REVOKE CREATE IN COLLECTION
 For details, refer to Revoking privileges with the REVOKE statement.
Add object to instance DBA userid 
SELECT <…> FROM
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSRESAUTH
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSROUTINES
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSTABLES
SYSIBM.SYSTABLESPACE
SYSIBM.SYSDATABASE
SYSIBM.SYSRELS
SYSIBM.SYSINDEXES
SYSIBM.SYSTRIGGERS
SYSIBM.SYSAUXRELS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSTABLESPACESTATS
SYSIBM.SYSINDEXSPACESTATS
 SELECT catalog tables from the list.
For details, refer to 
CREATE <object>
 For details, refer to CREATE DATABASE statement.
Apply object changes DBA userid 
SELECT <…> FROM
SYSIBM.SYSTRIGGERS
SYSIBM.SYSTABLES
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSINDEXES
SYSIBM.SYSTABLESPACE
SYSIBM.SYSAUXRELS
SYSIBM.SYSDATABASE
SYSIBM.SYSRELS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSSEQUENCES
SYSIBM.SYSVIEWS
SYSIBM.SYSROUTINES
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSCHEMAAUTH
 SELECT catalog tables from the list.
For details, refer to 
ALTER <object>
 For details, refer to ALTER TABLE statement. 
DROP <object>
 For details, refer to DROP statement. 
CREATE <object>
 For details, refer to CREATE TABLE statement. 
UNLOAD/LOAD
 Unload and reload data for objects that undergo complex changes. 
Db2 utilities: COPY, REORG, RUNSTATS, CHECK DATA, REBUILD INDEX
 For details, refer to Db2 online utilities.
Merge DBA userid 
SELECT <…> FROM
SYSIBM.SYSTABLES
SYSIBM.SYSINDEXES
SYSIBM.SYSTRIGGERS
SYSIBM.SYSROUTINES
SYSIBM.SYSVIEWS
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSTABLESPACE
SYSIBM.SYSDATABASE
SYSIBM.SYSAUXRELS
SYSIBM.SYSRELS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSDEPENDENCIES
 SELECT catalog tables from the list.
For details, refer to 
ALTER <object>
 For details, refer to ALTER TABLE statement. 
DROP <object>
 For details, refer to DROP statement. 
CREATE <object>
 For details, refer to CREATE TABLE statement. 
GRANT BINDADD
 For details, refer to GRANT statement. 
GRANT CREATEALIAS
 For details, refer to GRANT statement. 
GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA
 For details, refer to GRANT statement. 
GRANT CREATE ON COLLECTION
For details, refer to GRANT statement.
Update instance from source DBA userid 
SELECT <…> FROM
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSRESAUTH
 SELECT catalog tables from the list.
For details, refer to 
DROP <object>
 For details, refer to DROP statement. 
CREATE <object>
 For details, refer to CREATE TABLE statement.