Creating Tables: The CREATE FILE Command

The CREATE FILE command uses existing Master and Access Files to generate new RDBMS tables and, possibly, unique indexes.

In this section: CREATE FILE Prerequisites and Processing How to: Create a Table Example: Using CREATE FILE to Create a DB2 Table Using CREATE FILE to Create a Teradata Table Using CREATE FILE to Create an IDMS SQL Table Using CREATE FILE to Create an Oracle Table

Syntax: How to Create a Table

CREATE FILE name

where:

name

Is the name of the Master and Access Files.

CREATE FILE Prerequisites and Processing

Before issuing the CREATE FILE command, make sure you have:

• RDBMS GRANT authority to create tables (as described in Connection, Authentication, and Security).
• A Master File. Field declarations describing the primary key columns must be listed first.
• An Access File. If KEYS is greater than zero, a unique index will be created. To create the index in descending order, set KEYORDER to HIGH.

If the Access File does not include a DBSPACE value, you can issue the SET DBSPACE command to establish a default tablespace or dbspace for the duration of the FOCUS session. (Consult Describing Tables to FOCUS, for the DBSPACE attribute and Environmental Commands, for the SET DBSPACE command.)

If you do not issue the SET DBSPACE command, CREATE FILE uses the data adapter installation default. If your site did not establish a default during installation:

• For DB2, the table is placed in the default DB2 database, DSNDB04, and DB2 dynamically creates the tablespace.
• For DB2 for VM, the table is placed in a private dbspace owned by the user. If none exists, an error message is returned.
• For Teradata, the table is placed into the database associated with the userid.
• For IDMS/SQL, the table is placed in the IDMS default area specified in the table's schema DDL definition.
• For Oracle, the table is placed in the default tablespace of the owner of the schema containing the table

When the table is successfully generated, the FOCUS command level prompt (>) appears.

The data adapter generates one table and one unique index (provided the KEYS parameter is not 0) for every segment declaration in a multi-table Master File. It accomplishes this in a single logical unit of work, so if one of the tables already exists, it does not create the others. That is, the FOCUS CREATE FILE command does not overwrite an existing RDBMS table as it may do for a FOCUS database.

Note: You can control index space parameters for DB2, Oracle, and IDMS/SQL with the data adapter SET IXSPACE command described in Environmental Commands.

You have two choices if an SQL error occurs:

• Change the value of the TABLENAME keyword in the Access File and reissue the CREATE FILE command.
• Discard the existing table with the SQL DROP command and reissue the CREATE FILE command.

You can also create tables by:

• Issuing the native SQL CREATE TABLE command from within the FOCUS environment. Consult Direct SQL Passthru, for Direct SQL Passthru.
• Using the HOLD FORMAT SQLengine option in a report request. See Advanced Reporting Techniques, for information about extract files.

Example: Using CREATE FILE to Create a DB2 Table

The following DB2 session illustrates table creation. The member name for the pair of file descriptions is EMPINFO. In order to trace the process, the example uses the SQLCALL component of the trace facility. (For more information about the data adapter trace facilities, see Tracing Data Adapter Processing.)

Since the EMPINFO Master and Access Files exist (see File Descriptions and Tables), the CREATE FILE command can create the EMPINFO table:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLCALL//CLIENT
CREATE FILE EMPINFO

The trace displays the SQL statements issued by the data adapter:

>>> RRSET   entered. (SQL), Gfun=  3, fun=  2; CMD_OPEN.
>>> RRSET   Count=  0
<<< RRSET   exited.  (SQL), Errcode=        0; CMD_OPEN.
<<< RRSET   Count=  1
>>> RRSET   entered. (SQL), Gfun=  1, fun=  6; EXECUTE_IM.
>>> RRSET   SQL: CREATE TABLE USER1."EMPINFO"( "EID" CHAR (0009) NOT NULL,
>>> RRSET   SQL: "LN" CHAR (0015) NOT NULL ,"FN" CHAR (0010) NOT NULL ,"HDT"
>>> RRSET   SQL: DATE  NOT NULL ,"DPT" CHAR (0010),"CSAL" DECIMAL(07, 02) NOT
>>> RRSET   SQL: NULL ,"CJC" CHAR (0003) NOT NULL ,"OJT" REAL ,"BONUS_PLAN"
>>> RRSET   SQL: INTEGER  NOT NULL ,"HDTT" TIMESTAMP NOT NULL ,"HT" TIME NOT
>>> RRSET   SQL: NULL ) IN PUBLIC.SPACE0
<<< RRSET   exited.  (SQL), Errcode=        0; EXECUTE_IM.
>>> RRSET   entered. (SQL), Gfun=  1, fun=  6; EXECUTE_IM.
>>> RRSET   SQL:  CREATE  UNIQUE INDEX USER1."EMPINFOIX" ON
>>> RRSET   SQL: USER1."EMPINFO" ("EID"  ASC) USING STOGROUP PMSSTGRP
<<< RRSET   exited.  (SQL), Errcode=        0; EXECUTE_IM.
>>> RRSET   entered. (SQL), Gfun=  1, fun=  5; COMMIT_WORK.
<<< RRSET   exited.  (SQL), Errcode=        0; COMMIT_WORK.
>>> RRSET   entered. (SQL), Gfun=  3, fun=  3; CMD_CLOSE.
>>> RRSET   Count=  1
<<< RRSET   exited.  (SQL), Errcode=        0; CMD_CLOSE.
<<< RRSET   Count=  0

The data adapter generates one SQL CREATE TABLE command that consists of:

• Column information from the field declarations in the Master File.
• Table information from the TABLENAME value in the Access File.

The new table resides in tablespace PUBLIC.SPACE0.

Since the KEYS value in the Access File is greater than zero, the data adapter issues the SQL CREATE UNIQUE INDEX command. The first n fields in the Master File and the KEYS value provide the required information.

The index EMPINFOIX is created in ascending order, the RDBMS default. Its name is composed of the table name and the suffix IX. The parentheses around the EID field from the Master File indicate that it is the column to be indexed.

If no SQL errors result from table or index creation, the data adapter issues the SQL COMMIT WORK command to permanently define the table and its index. If an error occurs, the data adapter issues an SQL ROLLBACK WORK command. The ROLLBACK WORK command returns the RDBMS catalog tables to their original state, and table generation stops.

In this example there are no errors, since each generated SQL statement returns an error code of 0. The data adapter issues the SQL COMMIT WORK command to permanently define the table and its index.

Example: Using CREATE FILE to Create a Teradata Table

The following session illustrates the table creation process. In order to trace the process, this example uses the SQLCALL component of the trace facility. (For more information about the data adapter trace facilities, see Tracing Data Adapter Processing.)

Since the EMPINFO Master and Access Files exist (see File Descriptions and Tables), the CREATE FILE command is issued to create the EMPINFO table.

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLCALL//CLIENT
CREATE FILE EMPINFO

The trace displays as:

>>> DBTFOC   entered. (SQLDBC), Gfun=  3, fun=  2; CMD_OPEN.
>>> DBTFOC   Count=  0
<<< DBTFOC   exited.  (SQLDBC), Errcode=        0; CMD_OPEN.
<<< DBTFOC   Count=  1
>>> DBTFOC   entered. (SQLDBC), Gfun=  1, fun=  6; EXECUTE_IM.
>>> DBTFOC   SQL:  CREATE TABLE USER1.EMPINFO( EID CHAR (0009) NOT NULL ,
>>> DBTFOC   SQL: LNAME CHAR (0015) NOT NULL ,FN CHAR (0010) NOT NULL ,HDT 
>>> DBTFOC   SQL: DATE  NOT NULL ,DPT CHAR (0010),CSAL DECIMAL(15, 02) NOT 
>>> DBTFOC   SQL: NULL ,CJC CHAR (0003) NOT NULL ,OJT FLOAT ,BONUS_PLAN
>>> DBTFOC   SQL: INTEGER  NOT NULL )  UNIQUE PRIMARY INDEX (EID)
<<< DBTFOC   exited.  (SQLDBC), Errcode=        0; EXECUTE_IM.
>>> DBTFOC   entered. (SQLDBC), Gfun=  3, fun=  3; CMD_CLOSE.
>>> DBTFOC   Count=  1
<<< DBTFOC   exited.  (SQLDBC), Errcode=        0; CMD_CLOSE.
<<< DBTFOC   Count=  0

The resulting trace shows that the data adapter generated one DBC/SQL CREATE TABLE statement. The statement consists of:

• Column information from the field declarations in the Master File.
• Table information from the TABLENAME value in the Access File.
• Syntax for a unique primary index.

A unique primary index is created when the KEYS value in the Access File is greater than 0 (zero). The first n fields in the Master File and the KEYS value provide the required information. The field EID from the EMPINFO Master File appears in parentheses as the column to be indexed.

Note: The Teradata RDBMS requires a primary index. If the KEYS value is not specified, the RDBMS creates a non-unique primary index on the first column in the table.

In the lower portion of the trace, the error code of 0 indicates success. When table creation fails, a specific DBC return code is displayed and the data adapter issues a DBC/SQL ROLLBACK WORK command. The ROLLBACK WORK command causes the DBC Directory to return to its original state and table generation stops.

Example: Using CREATE FILE to Create an IDMS SQL Table

The following IDMS/SQL session illustrates table creation. The member name for the pair of file descriptions is EMPINFO. In order to trace the process, the example uses the SQLCALL component of the trace facility. (For more information about the data adapter trace facilities, see Tracing Data Adapter Processing.)

Since the EMPINFO Master and Access Files exist (see File Descriptions and Tables), the CREATE FILE command can create the EMPINFO table:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLCALL//CLIENT
CREATE FILE EMPINFO

The trace displays as follows:

>>> IDQFOC  entered. (SQLIDMS), Gfun=  3, fun=  4; REFRECH_DI.
<<< IDQFOC  SQLFLAGS: 00000000 00000000 0000004D 80000000 00000001 00000002
<<< IDQFOC  SQLFLAGS: 01000032 00000000 00000001 00000000 00000000 00000000
<<< IDQFOC  SQLFLAGS: 00000000 00000000 00000000 00000000 00000000 00000000
<<< IDQFOC  SQLFLAGS: 00000000 00000000 00000000 00000000 00000000 00000000
<<< IDQFOC  SQLFLAGS: 00000000 00000000 00000000 00000000 00000000 00000000
<<< IDQFOC  SQLFLAGS: 00000000 00000000 00000000 00000000 00000000 00000000
<<< IDQFOC  SQLFLAGS: 00000000 00000000 00000000
<<< IDQFOC  APTFLAGS: 000000C0 00000001 97D00001 00000001 00000000 3002D802
<<< IDQFOC  APTFLAGS: 00000002 00001A8B 00000000 00000000 0000000E 00000000
<<< IDQFOC  APTFLAGS: 00000000 00000000 00000004 00000001 00000000
<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; REFRECH_DI.
>>> IDQFOC  entered. (SQLIDMS), Gfun=  3, fun=  2; CMD_OPEN.
>>> IDQFOC  Count=  0
<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; CMD_OPEN.
<<< IDQFOC  Count=  1
>>> IDQFOC  entered. (SQLIDMS), Gfun=  1, fun=  6; EXECUTE_IM.
>>> IDQFOC  SQL:  CREATE TABLE EMPSCHEM."EMPINFO"( EID CHAR (0009) NOT NULL ,
>>> IDQFOC  SQL: LN CHAR (0015) NOT NULL ,FN CHAR (0010) NOT NULL ,HDT DATE
>>> IDQFOC  SQL: NOT NULL ,DPT CHAR (0010),CSAL DECIMAL(15, 02) NOT NULL ,CJC
>>> IDQFOC  SQL: CHAR (0003) NOT NULL ,OJT FLOAT ,BONUS_PLAN INTEGER  NOT
>>> IDQFOC  SQL: NULL )
<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; EXECUTE_IM.
>>> IDQFOC  entered. (SQLIDMS), Gfun=  1, fun=  6; EXECUTE_IM.
>>> IDQFOC  SQL:  CREATE  UNIQUE INDEX "EMPINFOIX" ON EMPSCHEM."EMPINFO"
>>> IDQFOC  SQL: (EID  ASC)

<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; EXECUTE_IM.
>>> IDQFOC  entered. (SQLIDMS), Gfun=  1, fun=  5; COMMIT_WORK.
<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; COMMIT_WORK.
>>> IDQFOC  entered. (SQLIDMS), Gfun=  3, fun=  3; CMD_CLOSE.
>>> IDQFOC  Count=  1
<<< IDQFOC  exited.  (SQLIDMS), Errcode=        0; CMD_CLOSE.
<<< IDQFOC  Count=  0

The data adapter generates one SQL CREATE TABLE command that consists of:

• Column information from the field declarations in the Master File.
• Table information from the TABLENAME value in the Access File.

The new table resides in IDMS area EMPSEG.EMPAREA.

Since the KEYS value in the Access File is greater than zero, the data adapter issues the SQL CREATE UNIQUE INDEX command. The first n fields in the Master File and the KEYS value provide the required information.

The index EMPINFOIX is created in ascending order, the IDMS default. Its name is composed of the table name and the suffix IX. The parentheses around the EID field from the Master File indicate that it is the column to be indexed.

If no SQL errors result from table or index creation, the data adapter issues the SQL COMMIT WORK command to permanently define the table and its index. If an error occurs, the data adapter issues an SQL ROLLBACK WORK command. The ROLLBACK WORK command returns the IDMS system tables to their original state, and table generation stops.

In this example there are no errors, since each generated SQL statement returns an error code of 0. The data adapter issues the SQL COMMIT WORK command to permanently define the table and its index.

Example: Using CREATE FILE to Create an Oracle Table

The following example shows how the data adapter creates the Oracle table EMPINFO using the CREATE FILE command with the sample EMPINFO Master and Access File. (For information about the data adapter trace facilities, see Tracing Data Adapter Processing.)

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLCALL//CLIENT
CREATE FILE EMPINFO

The trace follows:

>>> ORAFOC   entered. (SQLORA), Gfun=  3, fun=  2; CMD_OPEN.
>>> ORAFOC   Count=  0
<<< ORAFOC   exited.  (SQLORA), Errcode=        0; CMD_OPEN.
<<< ORAFOC   Count=  1
>>> ORAFOC   entered. (SQLORA), Gfun=  1, fun=  6; EXECUTE_IM.
>>> ORAFOC   SQL:  CREATE TABLE USER1.EMPINFO( "EID" VARCHAR2 (0009) NOT NULL
>>> ORAFOC   SQL: ,"LN" VARCHAR2 (0015) NOT NULL ,"FN" VARCHAR2 (0010) NOT
>>> ORAFOC   SQL: NULL ,"HDT" DATE  NOT NULL ,"DPT" VARCHAR2 (0010),"CSAL"
>>> ORAFOC   SQL: DECIMAL(07, 02) NOT NULL ,"CJC" VARCHAR2 (0003) NOT NULL ,
>>> ORAFOC   SQL: "OJT" REAL ,"BONUS_PLAN" INTEGER  NOT NULL ,"HDTT" DATE NOT
>>> ORAFOC   SQL: NULL )
<<< ORAFOC   exited.  (SQLORA), Errcode=        0; EXECUTE_IM.
>>> ORAFOC   entered. (SQLORA), Gfun=  1, fun=  6; EXECUTE_IM.
>>> ORAFOC   SQL:  CREATE  UNIQUE INDEX USER1.EMPINFOIX ON USER1.EMPINFO
>>> ORAFOC   SQL: ("EID"  ASC)
<<< ORAFOC   exited.  (SQLORA), Errcode=        0; EXECUTE_IM.
>>> ORAFOC   entered. (SQLORA), Gfun=  1, fun=  5; COMMIT_WORK.
<<< ORAFOC   exited.  (SQLORA), Errcode=        0; COMMIT_WORK.
>>> ORAFOC   entered. (SQLORA), Gfun=  3, fun=  3; CMD_CLOSE.
>>> ORAFOC   Count=  1
<<< ORAFOC   exited.  (SQLORA), Errcode=        0; CMD_CLOSE.
<<< ORAFOC   Count=  0

Note:

• This example assumes that the Oracle login ID is USER1. Since the login ID is the creator of the table, it is not really necessary to specify the creator in the Access File to use CREATE FILE.
• The Oracle table name is obtained from the Access File.
• The column definitions are taken from the USAGE attributes in the Master File.
• The unique index is based on the KEYS attribute in the Access File and the order of the field names in the Master File. In this case, KEYS=1, with EID as the first field described in the Master File. A unique index was created on the column EMP_ID.
• If the CREATE TABLE and CREATE UNIQUE INDEX commands are successful, the data adapter issues a COMMIT WORK to permanently store the table definition in the Oracle RDBMS.

Note: If the data adapter specifies SMALLINT, INTEGER or DECIMAL for a data type, Oracle responds by creating either full size NUMERIC or NUMERIC (n,m) columns. SMALLINT, INTEGER and DECIMAL are acceptable DB2 for VM keywords for use in creating Oracle tables and are used by the data adapter for CREATE FILE and certain other operations. There is no need for you to be familiar with them or to use them when creating your own tables.

The order of the columns will be the same as the order in which they were described in the Master File.