When maintaining large databases DELETE and UPDATE statements that process many rows may consume many resources, lock rows and grow the recovery-unit journal (RUJ). It is often more practical to break these tasks into smaller batches and have them report their progress periodically. This example module contains module procedures that can be used to construct useful maintenance procedures. These annotated examples process the table in a single pass, report progress using the TRACE statement, commit periodically and are restartable in case of failure.

Question: How long is the uncompressed table row when stored on disk?

Answer: The row consists of the user data, a null bit vector (sized according to the number of column id's), and some header information.

This sample code includes a procedure SHOW_ROW_LENGTH that calculates this length. A procedure REPORT_AIP_VARIANCE uses the system tables and this function to report any deviation from the length stored in the AIP (Area Inventory Page) for the logical area. This may indicate that added columns, or changed lengths have enlarged or reduced the row length. The effect could be a reduced efficiency in row INSERT.

This module references the Rdb INFORMATION table Rdb$LOGICAL_AREAS which can be defined in the database using SQL$SAMPLE:INFO_TABLES.SQL.

SQL> set flags 'trace,noprefix';
SQL>
SQL> call REPORT_AIP_VARIANCE ();
Table Name                     Actual     AIP Length
----------                     ------     ----------
CANDIDATES                        298            298
COLLEGES                           64             64
DEGREES                            37             37
DEPARTMENTS                        55             55
EMPLOYEES                         126            126
JOBS                               41             41
JOB_HISTORY                        42             42
RESUMES                            21             21
SALARY_HISTORY                     33             33
TABLE1                             78             78
TTT                                28             20         <****
WORK_STATUS                        31             31

The tables marked with <**** should be examined more closely
SQL>

Question: Is there a way to format DATE VMS values in stored procedures?

Answer: OpenVMS provides routines that will operate on DATE VMS types to format them and also parse them as input.

Normally, Oracle Rdb converts only canonical date/time to DATE VMS data (e.g. 2006041316350000), but these functions support the full range of OpenVMS formats. Refer to OpenVMS documentation for how to modify the format for the process using the logical names SYS$LANGUAGE, LIB$DT_FORMAT and LIB$DT_INPUT_FORMAT.

The default is ENGLISH language month names, and the standard OpenVMS punctuation for the date time separators.

The following SQL function translates from the Rdb system table type information to a simple string containing the SQL data type. The passed data can come from RDB$FIELDS (domains), RDB$FIELD_VERSIONS (columns) and RDB$PARAMETERS (routine parameters or module global variables.

Example:

SQL> create domain SAMPLE_TS timestamp(1);
SQL> create domain SAMPLE_CHAR char(25);
SQL> create domain SAMPLE_REAL float(25);
SQL> create domain SAMPLE_NUMERIC numeric(9,3);
%SQL-I-NO_NUMERIC, SAMPLE_NUMERIC is being converted from NUMERIC to INTEGER.
SQL> create domain SAMPLE_INTERVAL interval day(4) to second (1);
SQL>
SQL> select
cont> RDB$FIELD_NAME as Column_Name,
cont> UTIL_GET_DTYPE (RDB$FIELD_TYPE, RDB$FIELD_SCALE,
cont> RDB$FIELD_LENGTH, RDB$SEGMENT_LENGTH, RDB$FIELD_SUB_TYPE)
cont> from RDB$FIELDS
cont> where RDB$FIELD_NAME starting with 'SAMPLE';
COLUMN_NAME
SAMPLE_CHAR CHAR (25)
SAMPLE_INTERVAL INTERVAL DAY(4) TO SECOND(1)
SAMPLE_NUMERIC INTEGER (3)
SAMPLE_REAL DOUBLE PRECISION
SAMPLE_TS TIMESTAMP (1)
5 rows selected

Question: How do you convert hex strings to integers in SQL? How do you print integers in hex?

Answer: The following SQL script defines a SQL module that provides SQL function jackets to the OpenVMS Runtime library functions that convert to and from hex strings.

• The USAGE IS LOCAL clause is added to prevent these external procedures being used outside this module.
  
• This module could be modified to run under Rdb release V7.0 by removing the DEFAULT clauses on the external functions and passing the parameters explicitly, removing the USAGE IS LOCAL clause, and changing the SQL standard syntax of PARAMETER STYLE GENERAL to GENERAL PARAMETER STYLE.

Example:

SQL> select LONG_TO_HEX_STRING (cast(employee_id as integer)) from employees limit to 10 rows;

 000000A4
 000000A5
 000000A6
 000000A7
 000000A8
 000000A9
 000000AA
 000000AB
 000000AC
 000000AD
10 rows selected
SQL> select HEX_STRING_TO_LONG ('FACE') from rdb$database;

              64206
1 row selected

Question: Is there any way to save a symbol in DCL from a SQL script?

Answer: Yes, but you will have to call a VMS runtime library function to do,this job. It is very simple, as shown here.

SQL> create procedure SET_SYMBOL
cont>     in  :symbol VARCHAR (255)
cont>         by descriptor,
cont>     in  :value_string VARCHAR (255)
cont>         by descriptor)
cont>     language SQL;
cont>     external
cont>         name LIB$SET_SYMBOL
cont>         location 'SYS$SHARE:LIBRTL.EXE'
cont>         language GENERAL
cont>         parameter style GENERAL
cont>     comment is 'Create a DCL symbol from SQL';
SQL>
SQL> declare :my_value varchar (20);
SQL> begin set :my_value = 'Sample'; end;
SQL> begin call SET_SYMBOL ('MY_VALUE', :my_value); end;
SQL>
$ show symbol my_value
  MY_VALUE = "Sample"

Question: Is there any way to get the current process id in SQL?

Answer: Yes, you can create an external procedure and SQL function to return this information. The external procedure can call LIB$GETJPI to return the process identification in an OUT parameter and the SQL function can return the result as a function result.

Each routine is created inside a CREATE MODULE statement so that the combined functionality has a single name for use by GRANT and DROP MODULE.

Please note that the BIND ON CLIENT SITE clause is the default and must be used. Otherwise the LIB$GETJPI call will return the process id of the separate external routines server.