DBMS CONCURRENT EXEC
DBMS_CONCURRENT_EXEC Package#
The DBMS_CONCURRENT_EXEC package allows the concurrent execution of procedures. This is a system-defined package.
DBMS_CONCURRENT_EXEC Procedures and Functions#
The DBMS_CONCURRENT_EXEC package consists of the following procedures and functions.
| Procedures/Functions | Description |
|---|---|
| INITIALIZE | Initializes the DBMS_CONCURRENT_EXEC package and specifies the number of procedures that can be executed concurrently. |
| REQUEST | Requests the DBMS_CONCURRENT_EXEC package to run a procedure. |
| WAIT_ALL | Waits for the execution of all procedures, that were requested by the DBMS_CONCURRENT_EXEC package, to finish. |
| WAIT_REQ | Waits for the procedure corresponding to Request ID to finish. |
| GET_ERROR_COUNT | Returns the number of errors that occurred on the requested procedure. |
| GET_ERROR | Fetches the syntax, error code, and error message of the procedure corresponding to Request ID. |
| PRINT_ERROR | Prints the syntax, error code, and error message of the procedure corresponding to Request ID. |
| GET_LAST_REQ_ID | Returns the most recently executed Request ID that was successful. |
| GET_REQ_TEXT | Returns the procedure syntax corresponding to Request ID. |
| FINALIZE | Frees the memory that executed the DBMS_CONCURRENT_EXEC package, and initializes the package. |
Related Properties#
Properties related to the DBMS_CONCURRENT_EXEC package can be set in the altibase.properties file.
-
CONCURRENT_EXEC_DEGREE_MAX
-
CONCURRENT_EXEC_DEGREE_DEFAULT
-
CONCURRENT_EXEC_WAIT_INTERVAL
For more detailed information, please refer to the General Reference.
Restrictions#
The DBMS_CONCURRENT_EXEC package has the following restrictions.
- Only procedures that do not return results can be executed; functions that do not return results cannot be executed.
- Procedures with output parameters cannot be executed.
- Procedures or functions cannot make recursive calls. If a recursive call is made, the RECURSIVE_CALL_IS_NOT_ALLOWED exception is raised.
- Cannot be used in parallel queries.
- Executed procedures cannot be printed to the screen with PRINT or PRINTLN. Logs are written in $ALTIBASE_HOME/trc/altibase_qp.log.
INITIALIZE#
INITIALIZE initializes the DBMS_CONCURRENT_EXEC package and sets the number of procedures allowed to be executed in parallel. On omission, the value set for the CONCURRENT_EXEC_DEGREE_DEFAULT property is applied.
The maximum number of procedures allowed to be executed in parallel cannot exceed the value set for the CONCURRENT_EXEC_DEGREE_MAX property. If a number of procedures corresponding to CONCURRENT_EXEC_DEGREE_MAX is being executed in another session, 0 is returned and the function does not execute.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.INITIALIZE (in_degree INTEGER DEFAULT NULL );
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| in_dgree | IN | INTEGER | The number of procedures to be executed in parallel |
Return Value#
If successful, the number of procedures (DEGREE) that were set is returned. If the server failed to allocate resources, 0 is returned.
Exception#
The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests INTIALIZE.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
Initialize the DBMS_CONCURRENT_EXEC package and set the number of procedures to be executed in parallel to 4.
VARIABLE OUT_DEGREE INTEGER;
EXEC :OUT_DEGREE := DBMS_CONCURRENT_EXEC.INITIALIZE(4);
REQUEST#
REQUEST requests the DBMS_CONCURRENT_EXEC package to execute a procedure.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.REQUEST(text VARCHAR(8192) );
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| text | IN | VARCHAR(8192) | The procedure and procedure arguments |
Return Value#
If successful, Request ID is returned. Request ID is managed in the DBMS_CONCURRENT_EXEC package.
If unsuccessful, -1 is returned. However, it is still possible to fetch Request ID, and errors can be checked with the GET_ERROR function.
Exception#
The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
Request procedures in the DBMS_CONCURRENT_EXEC package to be executed in parallel.
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
WAIT_ALL#
WAIT_ALL waits until the execution of procedures to be executed in parallel are finished.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.WAIT_ALL( );
Return Value#
If successful, 1 is returned. If unsuccessful, -1 is returned.
Exception#
The following exception can occur when requesting WAIT_ALL from a procedure executed by the DBMS_CONCURRENT_EXEC package.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
The following is an example of waiting for all the procedures requested by the DBMS_CONCURRENT_EXEC package to complete.
VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL( );
WAIT_REQ#
This procedure waits until the operation of a specific procedure being processed in parallel to be completed in the DBMS_CONCURRENT_EXEC package.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.WAIT_REQ( req_id INTEGER);
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| req_id | IN | INTEGER | The Request ID corresponding to the procedure executed by packages. |
Return Value#
1 is returned when successfully executed.
If a request ID does not exist, -1 is returned.
Exception#
If a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function, the following exception can be occurred.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
This is an example of waiting until the procedure requested by 'REQ_ID1' to be completed in the DBMS_CONCURRENT_EXEC package.
VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_REQ(:REQ_ID1);
GET_ERROR_COUNT#
GET_ERROR_COUNT returns the number of errors that occurred during the execution of a requested procedure. To get an accurate count, call WAIT_ALL and then GET_ERROR_COUNT.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.GET_ERROR_COUNT( );
Return Value#
If successful, the number of errors is returned.
0 means that the execution of all requested procedures was successful.
Example#
The following exception may occur when a procedure executed in DBMS_CONCURRENT_EXEC package requests this functon.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
Get the number of errors that occurred during the execution of a procedure.
VARIABLE ERR_COUNT INTEGER;
VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL( );
EXEC :ERR_COUNT := DBMS_CONCURRENT_EXEC.GET_ERROR_COUNT( );
GET_ERROR#
GET_ERROR fetches the syntax, error code, and error message of the procedure corresponding to Request ID. To get accurate information, call WAIT_ALL, and then GET_ERROR.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.GET_ERROR(
req_id IN INTEGER,
text OUT VARCHAR(8192),
err_code OUT INTEGER,
err_msg OUT VARCHAR(8192));
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| req_id | IN | INTEGER | The Request ID corresponding to the procedure for which error information is to be fetched |
| text | OUT | VARCHAR(8192) | The syntax of the procedure |
| err_code | OUT | INTEGER | The error code |
| err_msg | OUT | VARCHAR(8192) | The error message |
Return Value#
If successful, Request ID is returned.
If neither Request ID exists nor an error occurred, -1 is returned.
Exception#
The following exception may occur when a procedure executed in DBMS_CONCURRENT_EXEC package requests this functon.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
Fetch the error that occurred during the execution of a procedure.
VARIABLE RC INTEGER;
VARIABLE TEXT VARCHAR(8192);
VARIABLE ERR_CODE INTEGER;
VARIABLE ERR_MSG VARCHAR(8192);
VARIABLE REQ_ID INTEGER;
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_REQ(:REQ_ID);
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.GET_ERROR( :REQ_ID, :TEXT, :ERR_CODE, :ERR_MSG);
PRINT_ERROR#
PRINT_ERROR prints the syntax, error code, and error message of the procedure corresponding to Request ID.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.PRINT_ERROR(req_id IN INTEGER);
Parameter#
| Name | In/Out | Data Type | Description |
|---|---|---|---|
| req_id | IN | INTEGER | The Request ID corresponding to the procedure to be printed |
Example#
The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
Print an error that occurred during the execution of a procedure.
VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL();
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID1);
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID2)
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID3);
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID4);
GET_LAST_REQ_ID#
GET_LAST_REQ_ID returns the most recently executed Request ID that was successful.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.GET_LAST_REQ_ID( );
Return Value#
If successful, the most recently executed Request ID is returned.
Exception#
There is no exception.
Example#
The following is an example of obtaining the ID of the procedure operation last requested through the DBMS_CONCURRENT_EXEC package.
VARIABLE LAST_REQ_ID INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :LAST_REQ_ID := DBMS_CONCURRENT_EXEC.GET_LAST_REQ_ID( );
GET_REQ_TEXT#
GET_REQ_TEXT returns the syntax of the requested procedure.
Syntax#
VARCHAR(8192) variable :=
DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(req_id IN INTEGER);
Parameter#
| Name | In/Out | Data Type | Description |
|---|---|---|---|
| req_id | IN | INTEGER | The Request ID corresponding to theprocedure for which syntax is to be returned |
Return Value#
If successful, the syntax of the procedure is returned.
If the Request ID does not exist, NULL is returned.
Exception#
There is no exception.
Example#
The following is an example of obtaining the procedure operation syntax requested through the DBMS_CONCURRENT_EXEC package.
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID1));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID2));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID3));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID4));
FINALIZE#
FINALIZE initializes the DBMS_CONCURRENT_EXEC package and frees used resources.
Syntax#
INTERGER variable :=
DBMS_CONCURRENT_EXEC.FINALIZE( );
Return Value#
If successful, 1 is returned.
Exception#
The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.
RECURSIVE_CALL_IS_NOT_ALLOWED
Example#
The following is an example of initializing the DBMS_CONCURRENT_EXEC package and returning the used system resources.
VARIABLE RC INTEGER;
VARIABLE REQ_ID INTEGER;
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :RC := DBMS_CONCURRENT_EXEC.FINALIZE( );