Trail: PROIV Documentation > Administration > Environment Guide > File Maintenance

The File Maintenance (SLFILES) and External File Maintenance (SLEFILES) fields described here have special meaning when defining SQL tables.

Prompt	Meaning
File	The File field defines the logical name to be used during function definition. It is used as the actual SQL table name in the absence of an Alt file name.
Alt	If the table name specified at the previous prompt has to be qualified (for example, owner.tablename) or has more characters than the File field permits. Then you must specify it at the Alt file name field. Note that several PROIV file definitions may reference (via the same Alt file name) the same SQL table. You can specify different pseudo-keys or a different set of columns.  This method can be used to influence the transparency mode statements built by PROIV.

The next three fields are found in the More Information window of the File Maintenance (SLFILES) screen.

Prompt	Meaning
Auto Seq?	Auto-sequenced files (tables) are fully supported, however a high performance penalty is paid in resequencing large numbers of records (rows).
Comp/Div?	Leave this field blank; company/division dependent files (tables) are not supported.
External Format?	Set this flag to Y.
Variable Name	This defines the logical name by which the SQL table column is to be referenced during function definition.  This is also used to define the actual SQL table column name in the absence of an Alternate field name. SQL columns may be defined as arrays.  To designate an array, specify the number of entries (between 2 and 254) within brackets following the variable name, for example, Address(4).
Typ	This must be A or N for key variables, and A, L or N for non-key variables no other data type is allowed.  The first file variable at least, must be a key variable. The order of the file variables is significant only in that all the key variables must appear first, and the order of the key variables affects the operation of SEL-xxx statements.

 

Defining a set of file variables as key variables does not create an index for those columns in the database; index creation is entirely at the discretion of the SQL Database Administrator (DBA). All the key variables are used in the WHERE clause of a Transparency mode SELECT statement. The set of key variables must uniquely identify individual rows in the table. PROIV uses the pseudo keys to retrieve individual rows and cannot handle duplicates.

Group-occurs is not supported by SQL databases, and Redefinition is redundant since the layout of rows is entirely controlled by the DBMS. Encryption would conflict with SQL data types.

PROIV file variable arrays are supported for SQL files.  They are defined and used from PROIV in the same way as for other file systems.  However, each array element maps to an SQL column name which is the element name (or alternate variable name if supplied), followed by the element index number in ASCII (without leading zeros or padding). Ensure that the generated SQL column name does not exceed the maximum column length allowed by the SQL database in use. Otherwise, the SQL table creation will fail. Ensure the following is generated:

file variable array name length <= max. SQL col name length – 3

 

For example, if a file variable array 'SQLARY' iswere defined in an SQL file definition (without an alternate column name) then the map would be as follows: 

 

You can specify numeric keys and test for them with the SEL-ONLY and SEL-PARTIAL commands. A numeric key field cannot be the auto-sequenced part of an auto-sequenced file. Also, entries must have been made at the External Type and External Format fields in External File Maintenance screen (see below) to specify the numeric data type of the numeric key field.

Prompt	Meaning
Null Permissible?	This field is found at the Variable More Information window. Values Y and N. Y specifies that variable is initialized or cleared to a null value. Then, when the column is written to the database, it is set to 'Null' if the value of the variable has not been changed by logic or assignment from another variable.
External Type and External Format	These two fields are displayed in expanded mode.  If not specified PROIV automatically inserts values dependent on the file variable type and length defined. (These parameters are used purely to control the translation of data between PROIV and the DBMS; the actual native format within the database is entirely DBMS specific.
Null permissible?	This prompt appears in the paging area in Expanded mode. Values are Y and N.  Y specifies that variable is initialized or cleared to a null value.  Then, when the column is written to the database, it is set to 'Null' if the value of the variable has not been changed by logic or assignment from another variable.
Alt Name	If, for any reason, the actual SQL table column name is different from the defined Variable Name.  Use the Alternate Variable Name field to specify the required mapping between the logical PROIV file variable name and the physical SQL table column name.

Table 15. External File Maintenance

You are asked if you want to create the DBA Script for the SQL Table.  PROIV does not currently support DBMS table creation from the PROIV file definition and this must be performed by a Database

Administrator (DBA).  However, a script that can be used to create the SQL table is created if a response of Y is entered here.  The script is created, as an ASCII text file containing the SQL statements required to create the table and indexes as necessary.  It is given the name tablename.SQL where tablename is the primary filename (not the alternate) and is held in the current default directory.

The script should be invoked outside of PROIV by your DBA. Note that for performance reasons, this file may need modification of field data types and/or indexes to suit the specific needs of your application.  For details of how to use the scripts to create tables, refer to your database specific documentation

Comment on this topic

Topic ID: 720100