SQL/MP Version Management Guide

Program Versions
Compaq NonStop™ SQL/MP Version Management Guide—429833-001
5-11
Retrieving SQL Program Versions
Ordinarily, if a program is registered in a version 1 or version 2 catalog, the FILEINFO
command returns zero values for the program’s PFV and PCV, meaning that program’s
PFV and PCV are undefined. An exception to this rule occurs if all the following
conditions are true:
•
The format version of a program was originally registered in a catalog of version
300 or newer.
•
The catalog was later downgraded to version 2.
•
The program has not been recompiled since the catalog was downgraded.
If all the above statements are true, the FILEINFO command returns the program’s
original PFV and PCV.
Using Catalog Queries
You can retrieve the format version and catalog version of a program from the
PROGRAMFORMATVERSION and PROGRAMCATALOGVERSION columns of the
PROGRAMS table. (Note that these columns are not available in catalogs older than
version 300 or to queries by SQL software older than version 300.)
As an illustration of how to use catalog queries, suppose that you want to determine the
program format and program catalog versions of a program named PROGX registered in
a version 315 catalog on subvolume \SYS1.$VOL1.CAT1. If the file that contains the
object code is named \SYS1.$VOL1.MYSVOL.PROGX, you can obtain the program’s
PFV and PCV by issuing the following SQLCI commands:
>> VOLUME \SYS1.$VOL1.CAT1 ;
>> SELECT PROGRAMFORMATVERSION, PROGRAMCATALOGVERSION
+> FROM PROGRAMS
+> WHERE PROGRAMNAME = “\SYS1.$VOL1.MYSVOL.PROGX”;
If the program format version is 315 and the program catalog version is 1, the previous
SQL sequence results in the following output:
PROGRAMFORMATVERSION PROGRAMCATALOGVERSION
-------------------- ---------------------
315 1
--- 1 row(s) selected.
Using RESTORE With DETAIL Option
If you restore a program from tape by running the RESTORE utility with the DETAIL
option set, the resulting display shows the format version and catalog version of the
restored program.
Note. Avoid querying the catalog to retrieve information such as the program format version or
the program catalog version when you are performing static SQL compilations. Queries such
as these can result in contention for the PROGRAMS table, possibly degrading performance. If
you do query the catalog and want your queries to work on newer versions of the catalog,
specify the column names explicitly; do not use SELECT * as your query.