SEND TABLE—IDMS access mode

Commands and Syntax › SEND TABLE—IDMS access mode

SEND TABLE—IDMS access mode

SEND TABLE stores information from the current or named report file as an SQL table.

Access mode:The syntax below is invalid when the access switch is set to OLQ.

Whatever data used to construct a report file can be stored as an SQL table.

Syntax:

►►─── SENd TABle ─┬─────┬─┬───────────┬─ sql-table-name ──────────────────────►
                  └─ = ─┘ └─ schema. ─┘

 ►─┬───────────────────────────────────────────────────────────────┬──────────►
   └─ REPort ─┬─────┬─ report-name ─┬────────────────────────────┬─┘
              └─ = ─┘               └─ USEr ─┬─────┬─ user-name ─┘
                                             └─ = ─┘

 ►─┬──────────────────────────────────────┬───────────────────────────────────►
   └─ DICtname ─┬─────┬─ dictionary-name ─┘
                └─ = ─┘
 ►─┬─────────────────────────────────┬────────────────────────────────────────►
   └─ TYPe ─┬─────┬─┬─ CREate ─────┬─┘
            └─ = ─┘ ├─┬─ APPend ─┬─┤
                    │ └─ ADD ────┘ │
                    └─ REPlace ────┘

 ►─┬────────────────────────────────────┬─────────────────────────────────────►
   └─ AREa ─┬─────┬─ segment.area-name ─┘
            └─ = ─┘
 ►─┬────────────────────────────────────────────────────────────────────────┬─►
   │                 ┌───────────────────────────────────────────────────┐  │
   │                 │     ┌──────────────┐                              │  │
   └─ INDices ─┬───┬─▼─ ( ─▼─ field-name ─┴─ ) ─┬──────────────────────┬─┴──┘
               └ = ┘                            │ ┌──────────────────┐ │
                                                └─▼─┬─ ASCending ◄ ─┬┴─┘
                                                    ├─ DEScending ──┤
                                                    └─ NOT ALLowed ─┘

 ►─┬───────────┬──────────────────────────────────────────────────────────────►
   ├─ SUMmary ─┤
   └─ DETail ──┘

 ►─┬────────────────────────────────────────────────────┬─────────────────────►◄
   └─ COLumns ─┬─────┬─( ─┬─ MAXimum ─────────────┬─ ) ─┘
               └─ = ─┘    │ ┌───────────────────┐ │
                          └─▼─ field-reference ─┴─┘

Parameters:

sql-table-name

The name of the SQL table to be saved in CA IDMS/DB.

schema

The name of the schema associated with the SQL table to be saved in CA IDMS/DB.

REPORT= report-name

Identifies the saved report containing the columns that define the SQL table. Report-name names a report that was created with the SAVE REPORT command. If REPORT=report-name is not specified, the SQL table is defined by using columns from the current report.

USER= user-name

Identifies the user who saved the named report. If USER=user-name is not specified, the report is retrieved from the current user's directory.

DICTNAME= dictionary-name

Specifies the name of the SQL catalog where the named SQL table is added.

TYPE=

Specifies whether the named SQL table is being created, added to, or replaced. Type can be:

CREATE—The SQL table is new and is being assigned an initial definition.
APPEND/ADD—New data is added to an existing table definition.
REPLACE—The SQL table already exists and is replaced by new data.

Note: When either TYPE=APPEND/ADD or TYPE=REPLACE is specified, the columns in the current report must be the same as the columns in the existing table definition.

AREA= segment.area-name

Names an alternative area to store the SQL table.

INDICES=

Defines characteristics of the index for the SQL table.

field-name—A single or concatenated key field. You can specify more than one field-reference value for a table.
ASCENDING/DESCENDING—The order in which record occurrences are connected to a set sorted by key value. The default is ASCENDING.
NOT ALLOWED—Duplicate sort keys are not allowed. The key must be unique.

SUMMARY

Specifies that summary report lines only be included in the ASF table.

Note: In addition to specifying SUMMARY, you can create a table which contains summary information only by specifying DISPLAY SUMMARY or by selecting SUMMARY ONLY from the Sort screen in menu mode. Any subsequent SEND TABLE will contain only the summary information.

DETAIL

Specifies that all detail report lines be included in the ASF table.

COLUMNS=

Specifies the report file columns included in the SQL table. Columns can be:

MAXIMUM—All sequential columns are saved as columns in the SQL table.
field-reference—The columns and the number of characters in each column saved in the SQL table.

Considerations:

When an SQL table is created, the names of the field columns in the report file are assigned to the columns in the table definition.

If CA OLQ headers have been assigned to any report fields, these headers are retained as column names; both dynamic headers and CA OLQ headers retrieved from the data dictionary can be assigned to columns in an SQL table.

For multiple line headers, CA OLQ uses the field name for the internal name.

Examples:

Send Table

The SEND TABLE command can be used to instruct CA OLQ to replace all data previously associated with an SQL table with new data occurrences from the current report file. The following example replaces the EMP-HOSPITAL table with a single record:

send table=employee.hospital dictname=empdict
  type=replace

 OLQ 102017   TABLE PROCESSING HAS BEEN SUCCESSFULLY COMPLETED

Send Table Indices

This example presents the use of INDICES to define the characteristics of the index set for the SQL table:

send table=emp-salary
 indices=(emp-id-0415) not allowed
 (emp-last-name-0415, emp-first-name-0415)

For more information:

Global Syntax