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.

Create a Table

CREATE FILE name [DROP]

where:

name

Is the name of the Master and Access Files.

DROP

Drops the table, if it already exists, and then creates it.

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 Adapter Commands for the SET DBSPACE command.)

    If you do not issue the SET DBSPACE command, CREATE FILE uses the 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 Teradata, the table is placed into the database associated with the user ID.
    • 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 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 unless you specify the DROP option in the CREATE FILE command. That is, the FOCUS CREATE FILE command does not, by default, 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 adapter SET IXSPACE command described in Adapter Commands.

You have two choices if an SQL error occurs:

  • Issue the CREATE FILE command with the DROP option.
  • Change the value of the TABLENAME attribute 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.

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 adapter trace facilities, see Tracing Adapter Processing.)

Since the EMPINFO Master and Access Files exist (see File Descriptions and Tables), the CREATE FILE command can create the EMPINFO table. If the table already exists, it will be dropped first:

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

The trace displays the SQL statements issued by the adapter:

RRSET10  entered. (DB2), Gfun=  3, fun=  2; CMD_OPEN.                     
RRSET10  Count=  0                                                        
RRSET10  exited.  (DB2), Errcode=        0; CMD_OPEN.                     
RRSET10  Count=  1                                                        
RRSET10  entered. (DB2), Gfun=  1, fun=  6; EXECUTE_IM.                   
RRSET10  SQL:  DROP TABLE USER01."EMPINFO"                                
RRSET10  exited.  (DB2), Errcode=        0; EXECUTE_IM.                   
RRSET10  entered. (DB2), Gfun=  1, fun=  5; COMMIT_WORK.                  
RRSET10  exited.  (DB2), Errcode=        0; COMMIT_WORK.                  
RRSET10  entered. (DB2), Gfun=  1, fun=  6; EXECUTE_IM.                   
RRSET10  SQL:  CREATE TABLE USER01."EMPINFO"( "EID" CHAR (9) NOT NULL ,   
RRSET10  SQL: "LN" CHAR (15) NOT NULL ,"FN" CHAR (10) NOT NULL ,"HDT" DATE
RRSET10  SQL:  NOT NULL ,"DPT" CHAR (10),"CSAL" DECIMAL(7, 2) NOT NULL ,  
RRSET10  SQL: "CJC" CHAR (3) NOT NULL ,"OJT" REAL ,"BONUS_PLAN" INTEGER   
RRSET10  SQL: NOT NULL ,"HDTT" TIMESTAMP,"HT" TIME) IN DBUSER01.FOCUS     
RRSET10  exited.  (DB2), Errcode=        0; EXECUTE_IM.                   
RRSET10  entered. (DB2), Gfun=  1, fun=  6; EXECUTE_IM.                   
RRSET10  SQL:  CREATE  UNIQUE INDEX USER01."EMPINFOIX" ON USER01."EMPINFO"
RRSET10  SQL: ("EID"  ASC)                                                
RRSET10  exited.  (DB2), Errcode=        0; EXECUTE_IM.                   
RRSET10  entered. (DB2), Gfun=  1, fun=  5; COMMIT_WORK.                  
RRSET10  exited.  (DB2), Errcode=        0; COMMIT_WORK.                  
RRSET10  entered. (DB2), Gfun=  3, fun=  3; CMD_CLOSE.                    
RRSET10  Count=  1                                    
RRSET10  exited.  (DB2), Errcode=        0; CMD_CLOSE.
RRSET10  Count=  0

The 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 DBUSER01.FOCUS.

Since the KEYS value in the Access File is greater than zero, the 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 adapter issues the SQL COMMIT WORK command to permanently define the table and its index. If an error occurs, the 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 adapter issues the SQL COMMIT WORK command to permanently define the table and its index.

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 adapter trace facilities, see Tracing 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 USER01.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 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 0 indicates success. When table creation fails, a specific DBC return code is displayed and the 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.

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 adapter trace facilities, see Tracing 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
SET TRACESTAMP = OFF
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 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, 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 adapter issues the SQL COMMIT WORK command to permanently define the table and its index. If an error occurs, the 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 adapter issues the SQL COMMIT WORK command to permanently define the table and its index.

Using CREATE FILE to Create an Oracle Table

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

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLCALL//CLIENT
SET TRACESTAMP = OFF
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 USER01.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 USER01.EMPINFOIX ON USER01.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 USER01. Since the login ID is the creator of the table, it is not really necessary to specify the creator in the Access File in order 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 adapter issues a COMMIT WORK to permanently store the table definition in the Oracle RDBMS.
Note: If the 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 keywords for use in creating Oracle tables and are used by the 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.