Creating Tables: The CREATE FILE Command

In this section:

How to:

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


Top of page

x
Syntax: How to 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.


Top of page

x
CREATE FILE Prerequisites and Processing

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

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:

You can also create tables by:



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 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:

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.



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 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:

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.



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 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:

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.



Example: 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:

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.


Information Builders