To create DB2® routines, you must have certain authorities and privileges, depending on your operating system. Contact your DB2 database administrator to obtain the correct authorities and privileges.
Action | Required authority |
---|---|
Access target databases | CONNECT |
Register stored procedures with a database server | CREATE PROCEDURE And one of the following privileges:
|
Register user-defined functions with a database server | CREATE FUNCTION |
Retrieve rows from a table or view | SELECT |
Create a view on a table | SELECT |
Run the EXPORT utility | SELECT |
Insert an entry in a table or view, and run the IMPORT utility | IMPORT |
Change an entry in a table, a view, or one or more specific columns in a table or view | UPDATE |
Delete rows from a table or view | DELETE |
Test a stored procedure or user-defined function | SYSADM or DBADM EXECUTE or CONTROL privilege for the package that is associated with the stored procedure (for SQL stored procedures or Java™ stored procedures with embedded SQL) |
Drop a stored procedure | You must own the stored procedure and have at least one of the following
authorities:
|
To provide DB2 development features, the workbench accesses DB2 system catalog tables. The user ID that is specified for a connection in the workbench must have the following privileges:
For DB2 for z/OS and OS/390 Version 7 and DB2 for z/OS Version 8, the workbench accesses the following tables:
Note: the workbench does not directly write to the tables listed above. The REXX stored procedure DSNTPSMP performs the writing. Therefore, the user ID that is specified for a connection also requires the following authorities:
To use a secondary ID, the following privileges must be granted to the secondary ID:
After these privileges are granted, you can create stored procedures by adding the OWNER keyword to the BIND options. The format for this option is OWNER(secondary ID).
You also need access to specific data sets defined in the WLM environment in which the procedure DSNTPSMP is running. The data set names can vary from site to site, depending on how they are defined in the WLM JCL that they are running.