The SET CALLTYPE command enables you to set the data retrieval type per session, file, or segment by switching dynamically between physical and logical reads.

The following examples illustrate the use of variations of dynamic CALLTYPE syntax:

ENGINE ADBSINX SET CALLTYPE FIND

Uses FIND as the type of data retrieval call for all files.

ENGINE ADBSINX SET CALLTYPE RL AFD EMPFILE

Retrieves data for the EMPFILE using RL as the type of data retrieval call.

Note that you can issue several commands for different files. Each command must be issued separately. Changes are cumulative. For example, the following commands retrieve data for the EMPFILE using FIND as the type of data retrieval call for a particular ADBS segment (segment S02):

ENGINE ADBSINX SET CALLTYPE RL AFD EMPFILE

ENGINE ADBSINX SET CALLTYPE FIND AFD EMP2

ENGINE ADBSINX SET CALLTYPE MIXED AFD VEHICLES

ENGINE ADBSINX SET CALLTYPE FIND AFD EMPFILE SEGNAME S02

ENGINE ADBSINX SET ?

Displays your settings.

ENGINE ADBSINX SET CALLTYPE DEFAULT

Returns to the default settings. This command resets all SET CALLTYPE commands for all Access Files.

You can use the FETCHJOIN command to take advantage of Multifetch efficiencies in Join operations for a session. This feature is particularly useful when you are joining to a base file in which sorting is based on the key field that is used for the join.

ENGINE ADBSINX SET FETCHJOIN {ON|OFF}

Adabas includes two data definition options, Not Counted (NC) and Not Null or Null Value Not Allowed (NN), for providing SQL-compatible null representation for Software AG's mainframe Adabas SQL Server (ESQ) and other Structured Query Language (SQL) database query languages.

The NC and NN options cannot be applied to fields defined:

• With Adabas null suppression (NU).
• With a fixed-point data type (FI).
• With multiple-values (MU).
• Within a periodic group.
• As group fields.

NC: SQL Null Value Option

Without the Not Counted (NC) option, a null value is either zero or blank depending on the field format.

With the NC option, zeros or blanks specified in the record buffer are interpreted according to the "null indicator" value: either as true zeros or blanks (that is, as "significant" nulls) or as undefined values (that is, as true SQL or "insignificant" nulls).

If the field defined with the NC option has no value specified in the record buffer, the field value is always treated as an SQL null.

Note: On the mainframe platform, subdescriptors and superdescriptors defined with NC=YES and MISSING=ON parameters cannot be used to search for the SQL NULL value, as that causes an Adabas RC 61.

NN: SQL Not Null Option

The Not Null or Null Value Not Allowed (NN) option may only be specified when the NC option is also specified for a data field. The NN option indicates that an NC field must always have a value (including zero or blank) defined; it cannot contain "no value".

The Create Synonym facility adds the parameter NC=YES in the Access File for fields defined with the NC option.

If a field with the NC option does not have the NN option, then the parameter MISSING=ON is also added to the field definition in the Master File. Subdescriptors and superdescriptors derived from this field will be defined as the fields with the following options: TYPE=NOP, NC=YES, and MISSING=ON. No component fields will be associated with these subdescriptors and superdescriptors.

Sample Master File and Access File contents with the NC option:

Master File:

FILENAME=ADANC1, SUFFIX=ADBSINX , $
  SEGMENT=S01, SEGTYPE=S0, $
    FIELDNAME=AA_FIELD, ALIAS=AA, USAGE=I6, ACTUAL=I2, FIELDTYPE=I, $
    FIELDNAME=AB_FIELD, ALIAS=AB, USAGE=I6, ACTUAL=I2, FIELDTYPE=I, MISSING=ON, $
    FIELDNAME=AC_FIELD, ALIAS=AC, USAGE=I6, ACTUAL=I2, FIELDTYPE=I, $
    FIELDNAME=AD_FIELD, ALIAS=AD, USAGE=I6, ACTUAL=I2, MISSING=ON, $
    FIELDNAME=AE_FIELD, ALIAS=AE, USAGE=I6, ACTUAL=I2, $
    FIELDNAME=BA_FIELD, ALIAS=BA, USAGE=A2, ACTUAL=A2, FIELDTYPE=I, $
    FIELDNAME=BB_FIELD, ALIAS=BB, USAGE=A2, ACTUAL=A2, FIELDTYPE=I, MISSING=ON, $
    FIELDNAME=BC_FIELD, ALIAS=BC, USAGE=A2, ACTUAL=A2, FIELDTYPE=I, $
    FIELDNAME=BD_FIELD, ALIAS=BD, USAGE=A2, ACTUAL=A2, MISSING=ON, $
    FIELDNAME=BE_FIELD, ALIAS=BE, USAGE=A2, ACTUAL=A2, $
   GROUP=G1_GROUP, ALIAS=G1, USAGE=A6, ACTUAL=A6, $
    FIELDNAME=CA_FIELD, ALIAS=CA, USAGE=A2, ACTUAL=A2, $
    FIELDNAME=CB_FIELD, ALIAS=CB, USAGE=A2, ACTUAL=A2, MISSING=ON, $
    FIELDNAME=CC_FIELD, ALIAS=CC, USAGE=A2, ACTUAL=A2, $
$ $$$$$$$$$$$$$$$$$$$$$$ Superdescriptor $$$$$$$$$$$$$$$$$$$$$$$$$$ $
    FIELDNAME=S1_FIELD, ALIAS=S1, USAGE=A6, ACTUAL=A6, FIELDTYPE=I, MISSING=ON, $
  $ FIELD=BA_FIELD_S01       ,ALIAS=BA         ,A2    ,A2    ,INDEX=I,$
  $ FIELD=BB_FIELD_S01       ,ALIAS=BB         ,A2    ,A2    ,INDEX=I,$
  $ FIELD=BE_FIELD_S01       ,ALIAS=BE         ,A2    ,A2    ,       ,$
$ $$$$$$$$$$$$$$$$$$$$$$ Superdescriptor $$$$$$$$$$$$$$$$$$$$$$$$$$ $
     FIELDNAME=S2_FIELD, ALIAS=S2, USAGE=A6, ACTUAL=A6, FIELDTYPE=I, MISSING=ON, $
$ FIELD=BA_FIELD_S02       ,ALIAS=BA         ,A2    ,A2    ,INDEX=I,$
$ FIELD=BC_FIELD_S02       ,ALIAS=BC         ,A2    ,A2    ,INDEX=I,$
$ FIELD=BD_FIELD_S02       ,ALIAS=BD         ,A2    ,A2    ,       ,$
$ $$$$$$$$$$$$$$$$$$$$$$$ Subdescriptor $$$$$$$$$$$$$$$$$$$$$$$$$$$ $
      FIELDNAME=S3_FIELD, ALIAS=S3, USAGE=A1, ACTUAL=A1, FIELDTYPE=I, MISSING=ON, $
$ $$$$$$$$$$$$$$$$$$$$$$$ Subdescriptor $$$$$$$$$$$$$$$$$$$$$$$$$$$ $
    FIELDNAME=S4_FIELD, ALIAS=S4, USAGE=A1, ACTUAL=A1, FIELDTYPE=I, $
$

Access File:

 RELEASE=6, OPEN=YES, $
 SEGNAM=S01, ACCESS=ADBS, FILENO=120, DBNO=3, CALLTYPE=FIND,
   UNQKEYNAME=AA_FIELD, WRITE=YES, $
$   CALLTYPE=RL  ,SEQFIELD=AA_FIELD                        ,$
$ FIELD=AA_FIELD ,TYPE=DSC       ,NC=   ,$
  FIELD=AB_FIELD, TYPE=DSC, NC=YES, $
  FIELD=AC_FIELD, TYPE=DSC, NC=YES, $
  FIELD=AD_FIELD, TYPE=, NC=YES, $
  FIELD=AE_FIELD, TYPE=, NC=YES, $
$ FIELD=BA_FIELD ,TYPE=DSC       ,NC=   ,$
  FIELD=BB_FIELD, TYPE=DSC, NC=YES, $
  FIELD=BC_FIELD, TYPE=DSC, NC=YES, $
  FIELD=BD_FIELD, TYPE=, NC=YES, $
  FIELD=BE_FIELD, TYPE=, NC=YES, $
  FIELD=CB_FIELD, TYPE=, NC=YES, $
  FIELD=CC_FIELD, TYPE=, NC=YES, $
  FIELD=S1_FIELD, TYPE=NOP, NC=YES, $
  FIELD=S2_FIELD, TYPE=NOP, NC=YES, $
  FIELD=S3_FIELD, TYPE=NOP, NC=YES, $
  FIELD=S4_FIELD, TYPE=NOP, NC=YES, $

Note: The following processing rules apply for fields defined with the NC=YES and MISSING=ON parameters:

• The default value is set to the SQL NULL value when a new record is created:

  SQL
  INSERT INTO ADANC1
     (AA_FIELD,   AB_FIELD,   AC_FIELD,
      BA_FIELD,   BC_FIELD,   BE_FIELD,
      CA_FIELD,   CC_FIELD)
      VALUES (21, 22, 23, 'B1', 'B3', 'B5','A2', 'C2');
  END

• The value could be set to the SQL NULL value when a record is updated:

  SQL
    UPDATE ADANC1 SET AB_FIELD=NULL 
    WHERE  AA_FIELD = 21;
  END

• These fields can be used in search criteria for selecting records that either have or do not have SQL NULL values, as in the following examples:

  SQL
    SELECT AA_FIELD, AB_FIELD, AC_FIELD, AD_FIELD, AE_FIELD FROM ADANC1
    WHERE  AB_FIELD IS NULL AND AA_FIELD GE 10;
  END
   
  SQL
    SELECT AA_FIELD, AB_FIELD, AC_FIELD, AD_FIELD, AE_FIELD FROM ADANC1
    WHERE  AB_FIELD IS NOT NULL;
  END
   
  TABLE FILE ADANC1R
    PRINT AA_FIELD AB_FIELD AC_FIELD AD_FIELD AE_FIELD G1_GROUP
    WHERE  AB_FIELD EQ MISSING;
  END

• Subdescriptors and superdescriptors can be used only in search criteria. Their values cannot be reported. Here are several examples:

  SQL
    SELECT AA_FIELD, AB_FIELD, AC_FIELD, AD_FIELD, AE_FIELD FROM ADANC1
    WHERE  S1_FIELD = 'A1A2A4' AND AA_FIELD > 10;
  END
   
  SQL
    SELECT AA_FIELD, AB_FIELD, AC_FIELD, AD_FIELD, AE_FIELD FROM ADANC1
    WHERE  S1_FIELD IS NOT NULL AND AA_FIELD > 10;
  END

Note: On the mainframe platform, subdescriptors and superdescriptors defined with the NC=YES and MISSING=ON parameters cannot be used to search for an SQL NULL value, as that causes an Adabas RC 61.

PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

ENGINE INT SET PASSRECS {ON|OFF}

The Adapter for VSAM supports both direct and sequential retrieval of fixed- and variable-length records from RRDS files, as well as modification of those records. RRDS files are VSAM files that are accessed through Relative Record Number keys (RRNs) and are processed in basically the same manner as key-sequenced (KSDS or KSEQ) VSAM files.

The RRN field that serves as the primary (unique) key for the segment follows all real fields in the physical root segment of the Master File and establishes the sequence for the records. The RRN field description must contain the following attributes:

ALIAS=RRN, USAGE=I10, ACTUAL=I4

Records in RRDS files are processed sequentially if the request does not provide a screening condition or join for the RRN-associated field.

FILE=RRDSVSAM, SUFFIX=VSAM, DATASET=PGMYAN.RRDSVSAM.CLUSTER, $
SEGNAME=ROOT ,SEGTYPE=S0 ,$
FIELD=CODE , ,USAGE=A2 ,ACTUAL=A2 ,$
FIELD=NAME , ,USAGE=A10 ,ACTUAL=A10 ,$
FIELD=VALUE , ,USAGE=P2 ,ACTUAL=Z1 ,$
FIELD=ACCESS_KEY ,ALIAS=RRN ,USAGE=I10 ,ACTUAL=I4 ,$Relative Record Number (RRN) key for VSAM;

TABLE FILE RRDSVSAM
PRINT CODE NAME VALUE ACCESS_KEY
END

TABLE FILE RRDSVSAM
PRINT CODE NAME VALUE ACCESS_KEY
IF ACCESS_KEY  GE 5
IF ACCESS_KEY  LE 15
END

TABLE FILE RRDSVSAM
PRINT CODE NAME VALUE ACCESS_KEY
IF ACCESS_KEY  EQ 5 OR 7 OR  9
END

MODIFY FILE RRDSVSAM ECHO
FREEFORM ACCESS_KEY
MATCH ACCESS_KEY
ON NOMATCH COMPUTE CODE = 'A1';
ON NOMATCH COMPUTE NAME = 'RECORD 20';
ON NOMATCH INCLUDE 
ON MATCH REJECT
DATA
ACCESS_KEY=20
END

MODIFY FILE RRDSVSAM ECHO
FREEFORM ACCESS_KEY
MATCH ACCESS_KEY
ON NOMATCH REJECT
ON MATCH
TYPE "CODE: <D.CODE>, NAME: <D.NAME>, VALUE: <D.VALUE>";
ON MATCH COMPUTE VALUE = 9;
ON MATCH UPDATE VALUE
DATA
ACCESS_KEY=20
END

MODIFY FILE RRDSVSAM ECHO
FREEFORM ACCESS_KEY
MATCH ACCESS_KEY
ON NOMATCH REJECT
ON MATCH
TYPE "CODE: <D.CODE>, NAME: <D.NAME>, VALUE: <D.VALUE>";
ON MATCH DELETE
DATA
ACCESS_KEY=7
END

You can create a report output file—that is, a HOLD file—as a native DBMS temporary table. This increases performance by keeping the entire reporting operation on the DBMS server, instead of downloading data to your computer and then back to the DBMS server.

For example, if you temporarily store report output for immediate use by another procedure, storing it as a temporary table instead of creating a standard HOLD file avoids the overhead of transmitting the interim data to your computer.

The temporary table columns are created from the following report elements

• Display columns
• Sort (BY) columns
• COMPUTE columns

except for those for which NOPRINT is specified.

The temporary table that you create from your report will be the same data source type (that is, the same DBMS) as the data source from which you reported. If the data source from which you reported contains multiple tables, all must be of the same data source type and reside on the same instance of the DBMS server.

You can choose between several types of table persistence.

You can create extract files as native DBMS tables with the following adapters:

• DB2 (on z/OS, UNIX, and Windows)
• Oracle
• Teradata

ON TABLE HOLD [AS filename]FORMAT SAME_DB [PERSISTENCE persistValue]

The adapter supports column descriptions of up to 255 characters.

The Adapter for Oracle now supports Oracle Release 10g. To install this version of the adapter, you must edit and submit member G10FORA in the FOCSQL.DATA data set. This member links Oracle with FOCUS.

Oracle 10g is stored in a PDSE data set. To link Oracle 10g to FOCUS, you must link it to a version of the FOCUS FOCLIB.LOAD data set that is also stored in a PDSE data set.

To run an interactive FOCUS session that accesses Oracle 10g, you must have a region size that is at least 32 megabytes.

Following is the G10FORA member of the FOCSQL.DATA data set. Add a job card, make the necessary edits, and submit this JCL to install the adapter:

//job card goes here 
//*********************************************************************
//* Name:     G10FORA JCL
//*
//* Function: Linkedit the MVS FOCUS/ORACLE 10  interface
//*
//* Substitutions:-Change "qualif" into the high level qualifier
//*                for your FOCUS datasets.
//*               -Change "qualpdse.LOAD" to the name of your
//*                FOCUS PDSE load library.
//*               -Change "oraqual.SQLLIB" TO THE NAME OF YOUR
//*                Oracle SQL library.
//*               -Change "libprfx" to the high level qualifier
//*                of your linker library (like CEE)
//*               -Change "plang" to the name of your linker
//*                message name (like EDCPMSGE)
//*********************************************************************
//*
//LKED1   EXEC PGM=IEWL,PARM='LIST,NOXREF,LET,RENT'
//OBJECT   DD DISP=SHR,DSN=qualif.FOCSQL.DATA
//SYSMODIN DD DISP=SHR,DSN=qualif.FOCSQL.LOAD
//SYSLMOD  DD DISP=SHR,DSN=qualpdse.LOAD
//SYSUT1   DD UNIT=SYSDA,SPACE=(100,(50,50))
//SYSPRINT DD SYSOUT=A
//LKED1.SYSLIN   DD *
  INCLUDE SYSMODIN(OR8FOC)
  INCLUDE OBJECT(OR8FOC)
  NAME ORAFOC(R)
/*

//*
//LKED2   EXEC PGM=IEWL,
//   PARM='LIST,NOXREF,LET,RENT,AMODE=31,RMODE=ANY,UPCASE'
//SYSLIB   DD DISP=SHR,DSN=libprfx.SCEELKED
//SYSMSGS  DD DSN=libprfx.SCEEMSGP(plang),DISP=SHR
//OBJECT   DD DISP=SHR,DSN=qualif.FOCSQL.DATA
//ORAOBJ   DD DISP=SHR,DSN=oraqual.SQLLIB
//SYSLMOD  DD DISP=SHR,DSN=qualpdse.LOAD
//SYSUT1   DD UNIT=SYSDA,SPACE=(100,(50,50))
//SYSPRINT DD SYSOUT=A
//LKED2.SYSLIN   DD *
  INCLUDE OBJECT(OR8PS2)
  INCLUDE OBJECT(ORAPAS10)
  INCLUDE ORAOBJ(ORASTBL)
  NAME ORAPAS2(R)
/*

where:

qualif

Is the high-level qualifier for your production FOCUS data sets.

qualpdse

Is the high-level qualifier for your FOCUS load library in PDSE format.

oraqual

Is the high-level qualifier for your Oracle SQL library.

libprfx

Is the high-level qualifier for your link edit load library.

plang

Is the linkage editor message member name.

Calculations can be processed differently in different RDBMSs and operating environments. If you want FOCUS to handle calculations instead of the RDBMS, you can issue the The SQL SET OPTIMIZATION NOAGGR command. This command disables optimization of calculations (DEFINE fields) without disabling optimization of join and sort operations.

SQL [engine] SET OPTIMIZATION NOAGGR

SQL Passthru is supported for Teradata macros and stored procedures.

Macros need to be developed within Teradata using the CREATE or REPLACE MACRO command. Procedures need to be developed within Teradata using the CREATE PROCEDURE command.

You must call a macro in the same transaction mode in which it was compiled. To find out which transaction mode is currently in effect, issue the HELP SESSION command:

SQL SQLDBC HELP SESSION;
TABLE FILE SQLOUT PRINT TRANSACTION_SEMANTICS
END

Before you can call a stored procedure or a macro, you must set the connection accordingly, by issuing the SET MACRO command

SQL SQLDBC SET MACRO {ON|OFF}

where:

ON

Enables one to call a macro. This is the default.

OFF

Enables one to call a stored procedure.

This is an example of the syntax for calling a macro:

ENGINE SQLDBC
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE FILE SQLOUT
END

The supported syntax to call a stored procedure is shown below.

ENGINE SQLDBC
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE FILE SQLOUT
END

When using the adapter with:

• ODBC, all scalar parameters (IN, OUT, and INOUT) are supported.
• CLI, scalar IN parameters, and INOUT parameters in IN mode, are supported.

CREATE OR REPLACE PACKAGE pack1 AS
TYPE nfrectype IS RECORD (
employee NF29005.EMPLOYEE_ID5%TYPE,
ssn5     NF29005.SSN5%TYPE,
l_name   NF29005.LAST_NAME5%TYPE,
f_name   NF29005.FIRST_NAME5%TYPE,
birthday NF29005.BIRTHDATE5%TYPE,
salary   NF29005.SALARY5%TYPE,
joblevel NF29005.JOB_LEVEL5%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(c_saltable IN OUT nfcurtype);
END pack1 ;
/
CREATE OR REPLACE PACKAGE BODY pack1 AS
PROCEDURE proc1 (c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT 
EMPLOYEE_ID5,SSN5,LAST_NAME5,FIRST_NAME5,BIRTHDAT
E5,SALARY5,JOB_LEVEL5 FROM NF29005;
END proc1 ;  -- end of procedure
END pack1; -- end of package body
/