Easytrieve Plus / DB2Course vs Dynamic Definitions and Data Communications Supported A - SQLCA Generated FieldsEasytrieve Plus /DB2Information Systems Training ISEasytrieve Plus /DB2Information Systems TrainingISCourse Pre-requisitesThis course provides no training in Easytrieve Plus nor in the use of DB2 and SQL.These topicsshould all be studied separately prior to this course.SQL (Structured Query Language) provides the method of access to DB2 data structures andremains the same no matter what application language or operating system is used.It is perfectlyreasonable to study DB2/SQL in a COBOL II environment before looking at its use in EasytrievePlus.Easytrieve Plus /DB2Information Systems Training Page 1ISStatic SQL and Dynamic SQLSQL is designed to allow specification of what data is required rather than how to access it.Theaccess paths to the data is worked out by DB2.
This may happen at two points in time:When the SQL statement is actually executed while the program is running (Dynamic SQL)In advance by using the SQL pre-processor on the source code (Static SQL)The Easytrieve Plus interface uses the Dynamic and Extended Dynamic interfaces supplied by IBM.The latter of these implements Static SQL commands.Only commands which can be executed usingthe Dynamic and Extended Dynamic interfaces can be embedded in an Easytrieve Plus program.NB.In Information Systems (BTC), programs may be run dynamically while in development, butmust be handed over to run statically in the live environment.Dynamic SQLTo run an Easytrieve Plus / DB2 program dynamically, simply use the
EZPGO catalogueprocedure to compile and run it all in one step.Easytrieve Plus /DB2Page 2Information Systems TrainingISStatic SQLBefore an Easytrieve Plus / DB2 program can be run using static SQL, it is first necessary to code aPARM LINK statement at the top of the program.The syntax of this is as follows:PARM LINK (program name [R]) +PLAN (plan name) +BIND (STATIC-ONLY)The R is short for replace, and causes an existing load module of the same name to be over-written.For the first compile of a program, the R can be left off, as the default is ADD.The program name and plan name MUST be different from one another.To run an Easytrieve Plus / DB2 program statically, it is necessary to use three separate catalogueprocedures.
The first step uses
DB2EZP to separate the program into its Easytrieve Plus and SQLcomponents.
This step produces an Easytrieve Plus load module, which is given the program name,and a Database Request Module (DBRM), which resides as a member in the partitioned datasetDEV@@xx.DB2.DBRMLIB.Also produced, but invisible to the developer, is an Assemblermodule, which is called dynamically at runtime to execute the SQL.
The second step of the process uses the Boots catalogue procedure DB2BIND to produce aPLAN from the DBRM.The PLAN is given the same name as the DBRM.Finally, the catalogued procedure TESTEZP is used to run the program.These steps are illustrated on the diagram overleaf.Easytrieve Plus /DB2Information Systems Training Page 3ISThe stages in running an Easytrieve Plus / DB2 program under static SQL.Easytrieve Plus /DB2Page 4Information Systems TrainingISEZY +LOAD MODULETESTEZP catprocDB2EZP catprocLoad Library =DEV@@xx.TEST.LOADLIBDBRM Library =Database Request ModuleAssembler ModuleSame name as DBRMDB2BIND catprocField Definitions and Data TypesAny data to be retrieved by Easytrieve Plus must be defined in the Library Section of the EasytrievePlus program.Such fields are called Host Variables.
They can either be working storage fields orfields in an active file.Host Variables are immediately preceded by a colon when they are referredto in an SQL statement.The following table shows SQL data types and their corresponding Easytrieve Plus field definitions:SQL data typeEasytrieve Plus field definitionINTEGER4 B 0SMALLINT2 B 0FLOATDECIMAL (precision, scale)x P y1CHAR (length)x AVARCHARLONG VARCHARnoneGRAPHIC (length)noneLONG VARGRAPHICnoneNUL indicator variables2 B 0nonex N ynonex B y2x U y
SQL does not support packed fields with a length greater than 8.2 SQL does not support binary fields other than 2 B 0 and 4 B 0.Easytrieve Plus /DB2Information Systems Training Page 5ISAutomatic and Controlled ProcessingA DB2 table can be accessed from an Easytrieve Plus program in one of two ways:Automatic ProcessingAll Easytrieve Plus programs must begin with a command such as FILE, PARM or DEFINE.Where there is no file input
- and therefore no FILE statement - it is necessary to code thecommand
DEFINE in front of the field definitions in the program.This statement is otherwiseoptional and would normally be left out.To specify that the job will have automatic input from SQL, it is first necessary to code the jobstatement as follows:JOB INPUT SQLA SELECT statement must then immediately follow the JOB statement.
The SELECT statementwill identify the rows and columns to be used as input to the job activity.NB.Only one SELECT
statement can be coded in each job activity.Using this method it is possible to retrieve selected data from every row within a DB2 table.eg.To select the contents of the column EMPNAME from the table PERSONNEL, one at a time,
using automatic input in Easytrieve Plus, code the following:DEFINE EMP-NAMEW 5 AJOB INPUT SQL SELECT EMPNAME +FROM PERSONNEL +INTO :EMP-NAME....
followed by other Easytrieve Plus commandsEasytrieve Plus /DB2Page 6Information Systems TrainingISControlled ProcessingAs with Automatic input, it is necessary to use the Easytrieve Plus DEFINE command on the firstline of the program if there is no input file.If controlled input is being used in an Easytrieve plus program, it is then necessary to define a cursorin the Library section.This is done using the SQL DECLARE statement, followed by a cursorname
.Any host variables referred to in a DECLARE statement must
Sql option guide
SQL Interface Option Guide A-EasytrieveThis documentation and related computer software program (hereinafter referred to as the Documentation) is for the end users informational purposes only and is subject to change or withdrawal by Computer Associates International, Inc.(CA) at any time.This documentation may not be copied, transferred, reproduced, disclosed or duplicated, in whole or in part, without the prior written consent of CA.
This documentation is proprietary information of CA and protected by the copyright laws of the United States and international treaties.
Notwithstanding the foregoing, licensed users may print a reasonable number of copies of this documentation for their own internal use, provided that all CA copyright notices and legends are affixed to each reproduced copy.Only authorized employees, consultants, or agents of the user who are bound by the confidentiality provisions of the license for the software are permitted to have access to such copies.This right to print copies is limited to the period during which the license for the product remains in full force and effect.Should the license terminate for any reason, it shall be the users responsibility to return to CA the reproduced copies or to certify to CA that same have been destroyed.To the extent permitted by applicable law, CA provides this documentation as is without warranty of any kind, including without limitation, any implied warranties of merchantability, fitness for a particular purpose or noninfringement.
In no event will CA be liable to the end user or any third party for any loss or damage, direct or indirect, from the use of this documentation, including without limitation, lost profits, business interruption, goodwill, or lost data, even if CA is expressly advised of such loss or damage.The use of any product referenced in this documentation and this documentation is governed by the end users applicable license agreement.The manufacturer of this documentation is Computer Associates International, Inc.Provided with Restricted Rights as set forth in 48 C.F.R.Section 12.212, 48 C.F.R.Sections 52.227-19(c)(1) and (2) or DFARS Section 252.227-7013(c)(1)(ii) or applicable successor provisions.
2001 Computer Associates International, Inc.All trademarks, trade names, service marks, and logos referenced herein belong to their respective 1: OverviewTopicsRelated PublicationsRevision Summary for DB2 for OS/390 and z/OS ElementsProgramming MethodsNative SQL StatementsAutomatic Cursor ManagementSQL Statement RulesChapter 2: Program EnvironmentUnits of WorkPARM Statement ParametersAll SQL EnvironmentsDB2 for OS/390 and z/OSDB2 for VSECA-Datacom/DBORACLEQualifying DB2 for OS/390 and z/OS TablesQualification with Automatic ProcessingQualification with Controlled ProcessingSQL Error HandlingSQL Error Message FormatSQL Error Message Text
iv SQL Interface Option Guide Chapter 3: Library Section DefinitionSQL Catalog INCLUDE FacilitySQL INCLUDE StatementSyntaxParametersField ReferenceQualifying SQL Column NamesProcessing NULLable FieldsManual NULL ProcessingSQL Data TypesDecimal Data TypesSQL Syntax CheckingSystem-Defined File Communications Area FieldsSample DatabaseWorking Storage DefinitionsChapter 4: Automatic ProcessingSpecifying Automatic InputFILE StatementJOB StatementSELECT StatementGenerated CodeChapter 5: Controlled ProcessingGET StatementSyntaxNative SQLSyntaxUsage NotesProcessing CALL StatementCALL Syntax: Format 1CALL Syntax: Format 2CALL, ASSOCIATE, and ALLOCATEChapter 6: Executing Your SQL ProgramDB2 for OS/390 and z/OS ExecutionDynamic SQL ModeStatic SQL ModeSample Job StreamPAN$SQL DD FileEZTPDB2 ProcedureExecution JCLProcedure OverridesJCL ModificationsMixed IMS and DB2 for OS/390 and z/OS ExecutionDB2 for VSE ExecutionCA-IDMS/SQL ExecutionCA-Datacom/SQL ExecutionORACLE ExecutionChapter 7: ExamplesAutomatic RetrievalAutomatic Retrieval: All ColumnsAutomatic Retrieval: Selected ColumnsAutomatic Retrieval: Multiple TablesAutomatic Retrieval: SQL FILEAutomatic Retrieval: SQL FILEAutomatic Retrieval: SQL FILE Using SQL FunctionsAutomatic Retrieval: SQL FILE With NULL IndicatorsAutomatic Retrieval: SQL FILE With Synchronized File ProcessingControlled RetrievalNative SQL: All ColumnsNative SQL: Reassign DepartmentsNative SQL: Update Phone NumbersNative SQL: SQL FILE
vi SQL Interface Option Guide Index Overview
11 Chapter This guide explains the optional product facilities that interface with the following SQL databases:
IBM DB2 for OS/390 and z/OS
IBM DB2 for VSE (SQL/DS)
Jasmine CA-Datacom/DB for SQL
Jasmine CA-IDMS SQL
ORACLE Before CA-Easytrieve Plus can process these databases, the Jasmine CA-Pan/SQL Interface product, version 2.4, must be correctly installed.See the CA-Pan/SQL SQL Interface Getting Started for complete information.To use this facility effectively, you should have a basic knowledge of SQL and the given database management system to be processed.This guide discusses the following topics:
Working environments for SQL
Host variable definitions
Automatic table processing
Native SQL commands and the GET command as used in controlled processing
JCL Related Publications 12 SQL Interface Option Guide Related Publications The following publication, produced by Computer Associates, is either referenced in this documentation or is recommended reading: CA-Pan/SQL SQL Interface Getting StartedThe following publications, not produced by Computer Associates, are either referenced in this documentation or are recommended reading: ORACLE Installation and System Administration GuideIBM DB2 Reference ManualRevision Summary for DB2 for OS/390 and z/OS Elements The SQL interface for has the following enhancements:
Support of DSNALI Above the Line
Release 4.1 of DB2 for OS/390 and z/OS linked DSNALI as AMODE 31.This caused DSNALI to be loaded Abo
Tags: easytrieve plus reference manual,easytrieve interview questions,easytrieve condition code 16,easytrieve for ims manual,easytrieve if statement,easytrieve plus user guide,ca easytrieve user guide,easytrieve user manual,easytrieve user guide