Using SQL statements in your DB2 application
To create, update, or delete data from a DB2 database, use SQL statements. SQL is a standardized language that allows you to define and manipulate data in a relational database. There are two ways to run SQL statements:
Statically
The source form of the statement is embedded within your Smalltalk application program, and the precompiler processes the statement at development time. The statement is prepared before the program runs, and the operational form of the statement persists beyond the processing of the application.
Dynamically
The source form of the statement is a character string passed to DB2 by an application program, and the statement runs at run-time. The statement is prepared during the processing of an SQL application, and the operational form of the statement is not persistent.
VA Smalltalk Server provides methods that enable you to perform the following SQL statements from CICS, IMS, or MVS:
• Static SELECT statement, through resultTableFromQuerySpec... methods
• Static non-SELECT statement, through executeQuerySpec
• Dynamic non-SELECT statement, through executeSQLStatement
Alternatively, you can use nonvisual parts for SQL statements. You use CLI or static SQL statements. For transactional environments such as CICS or IMS, you must use precompiled SQL statements.
The application AbtBaseStaticIbmDatabaseApp is required in both the development and runtime environments to write SQL statements that will run under DB2 in MVS:
The following applications are optional:
AbtEditStaticIbmDatabaseApp
Provides code for the development environment
TrailBlazerDB2
Provides TrailBlazer browser extensions that enable you to use the Trailblazer to work with your program's SQL statements
AbtSampleStaticIbmDatabase
Contains a full set of sample code for creating and modifying tables in a DB2 database
See Developing your DB2 application on the workstation for a description of the steps needed to test your application on the workstation. When your Smalltalk application is completely tested and ready to run in the target environment, follow the steps described in Deploying your application on the target environment.
Developing your DB2 application on the workstation
The following steps describe how to develop a DB2 Smalltalk application on the workstation. This process assumes that you will be precompiling your DB2 for workstation package specs from the development image. You can also choose to precompile your DB2 for workstation SQL packages from a passive image. To precompile for DB2 for workstation from a passive image, you need to version and release your classes in the development image, make a passive image for the target environment, load your application into the passive image, and precompile in the passive image.
Step 1: Creating an SQL statement
See DB2 SQL Reference and DB2 Application Programming and SQL Guide to learn how to write SQL statements. See Distributed Relational Database Architecture Formal Register of Existing Differences in SQL for a detailed description of the differences between SQL statements on different platforms.
If you are accessing DB2 on MVS, use high-level qualifiers.
Step 2: Coding SQL statements into a Smalltalk application
A Smalltalk DB2 application is designed and coded like any other Smalltalk application. The only additional work lies in the coding of a query spec for each static SQL statement.
Query specs are stored in access sets but are grouped together into package specs, each of which will become a DBRM on MVS or a bind file on the workstation. The DBRMs for an application are bound together (using DB2 Bind) into a plan. You can also bind DBRMs into intermediate packages and then into DB2 plans.
Each package spec is stored in a subclass of AbtDatabaseAccessSet. The package spec can be precompiled or its access set can be precompiled. When an access set is precompiled, all of the access set's package specs are precompiled. The TrailBlazer browser provides a user interface for the creation and maintenance of query specs, package specs, and access sets.
See Writing SQL statements using Smalltalk for more information.
Step 3: Precompiling your access sets for DB2 for workstation
Note:
You can precompile for DB2 for workstation from a workstation image only. That is, to precompile for DB2 for workstation, you must be either in the development image or in a passive image.
To use static SQL statements, you must precompile your SQL packages in Smalltalk before your application can be tested on the workstation. The precompile ensures that the SQL packages are in sync with the plan residing on DB2 for workstation. The precompile for DB2 for workstation does the following:
• Optionally creates a bind file (the default is not to create a bind file)
• Updates the plan
To precompile your access sets for the workstation using TrailBlazer, do the following:
1. Select Browse Applications from the Tools menu, and choose your application.
2. Change the middle pane, currently showing Classes, to display Access Sets.
3. Select the access set you wish to precompile, then, using the pop-up menu, select Precompile.
4. The first time you precompile, you will receive a selection window. The Trailblazer browser will prompt for the precompiler to use even when there is only one valid selection. Select the correct DB2 product for which to precompile the SQL package. Because you are precompiling in an image targeted for workstation testing, your only choice is the DB2 for workstation precompiler. You need to make this selection one time only for each new SQL package. Smalltalk saves the product selection you make so that each successive precompile does not require product selection.
Note that when precompiling for DB2, a trailing period in a host variable (which is an invalid host variable) causes a walkback.
Step 4: Testing your application on DB2 for workstation
When you have precompiled your SQL packages, you can test your program interactively against DB2 for workstation by running it from the Transcript or a workspace.
Deploying your application on the target environment
Once your program runs correctly on DB2 for workstation, you are ready to deploy it to the target environment.
Step 1: Releasing all classes to your application
You must release all classes to your application; otherwise, when you load your application into the passive image, the classes will not be loaded or will not contain the latest updates. To version and release all classes to your application in the development image, select Manage Applications from the Tools menu; then--
1. In the Application Manager, select your application from the selection list.
2. From the Classes menu, select Version/Release All > Use Defaults and press OK.
3. You can also version the application by selecting Version > Use Defaults from the Applications menu and pressing OK.
4. Close the Application Manager.
Step 2: Separating strings in your application
If you need to translate any strings in your application, you must separate the strings. To do so, follow instructions on National Language Support (NLS) in the Visual Programming User Guide and generate one or more message and pool repository (.mpr) files. This means you implement abtExternalizedStringBuildingInfo, loaded, and removing methods in your application class, then use menu choices available from the NLS choice of the Transcript's Tools menu.
Because your application will use one or more .mpr files, when packaging from an XD passive image, you will need to go to the Policies tab and select the Dumper policy type and the Make Message Catalogs Resident option. The Policies tab is available during the Modify Instructions step.
Step 3: Making the passive image the current image
If you do not already have a passive image, add one by following these steps:
1. From the XD menu of System Transcript, select New Image.
2. In the Image Properties window, change the following values:
a. For Image Type, select one of the following, depending on the platform on which the application is to run:
1. MVS
2. MVS Simulation (Windows target)
3. Windows
4. UNIX
5. MVS (Empty)
6. This example uses the choice MVS.
b. For Image Name, enter the name of this passive image. This example uses the name MVS Passive.
c. From the Installed Features list, select which features of VA Smalltalk Server you want pre-loaded into this passive image. Note that if you choose not to load certain features at this time, you can load the features into the image later.
d. When you have a development image and a passive image running at the same time, your screen can be filled with many windows. If you want the windows of only the current image to be displayed, click the box next to Hide Windows. For example, if you change from the development image to the passive image, all windows of the development image will be hidden while all passive image windows will be displayed.
e. See the Server Guide or the Programmer Reference for more information about setting the locale.
f. Press OK to create the passive image.
When you switch to the passive image--whether you have just created it or it has been previously created--the XD Transcript window opens. The XD Transcript is to this passive image what the System Transcript is to the development image. The XD Transcript looks like this:
Step 4: Loading your application into the passive image
To load your application into the passive image, select Manage Applications from the Tools menu of the XD Transcript window; then--
1. In the XD Application Manager, select Load > Available from the Applications menu.
2. Select your application or applications, indicating the edition you want loaded, and press the >> button to move the applications to the Selected Editions pane. Press OK.
3. Close the XD Application Manager.
Step 5: Precompiling your SQL packages for DB2 for MVS
Note:
You can precompile for DB2 for OS/390 from an OS/390 image only. That is, to precompile for DB2 for OS/390, you must be in a passive image whose image type supports OS/390.
Precompiling for DB2 for OS/390 differs from precompiling for DB2 for workstation. Although your SQL packages have been precompiled if you have tested them on the workstation, they need to be precompiled once again for use with DB2 for OS/390.
The precompiler updates information held by the existing instances of AbtQuerySpec and produces a DBRM for each SQL package. DB2 Application Programming and SQL Guide describes a set of precompiler options supported for PL/I, COBOL, and other languages. Where appropriate, options of the same name are also supported by the precompile behavior of AbtSqlPackage.
The following precompile options are used and cannot be changed for VA Smalltalk Server precompiles:
• APOST
• APOSTSQL
• DATE(ISO)
• PERIOD
• STDSQL(NO)
• TIME(ISO)
The following precompile options can be specified independently for each SQL package. Public class methods on AbtSqlPackage allow you to set and query these options:
• CONNECT (default is 1)
• SQL (default is DB2)
• SQLFLAG (default is 86)
• VERSION (default is blank)
The following precompile options do not apply to VA Smalltalk Server:
• DEC
• FLAG
• GRAPHIC/NOGRAPHIC
• HOST
• LEVEL
• LINECOUNT
• MARGINS
• NOFOR
• ONEPASS/TWOPASS
• OPTIONS/NOOPTIONS
• SOURCE/NOSOURCE
• XREF/NOXREF
To precompile your SQL packages for all target environments, do the following in the passive image:
1. From the Tools menu, select Browse Applications and choose your application.
2. Change the middle pane, currently showing Classes to display Access Sets.
3. Select the access set you wish to precompile, then, using the popup menu, select Precompile. The first time you precompile for OS/390, you will be prompted to choose an OS/390 precompiler.
4. If you have installed V5.1 on OS/390, choose OS/390 DB2 V4.1. The V4.1 precompiler is compatible with the V5.1 database.
The precompile will cause class methods to be regenerated for query specs and methods with SQL code to be precompiled. This requires that your application is an open edition and that you are the developer of the access set class.
Also, the precompile action generates a cross reference report showing the SQL generated by the query specs. The SQL is listed by generated statement number (the statement number referenced by many BIND error messages). The report can be seen by browsing the file named package.xrf, where package is the name of the DBRM or bind file created by the precompile.
Step 6: Binding to create a plan
The DBRM created as part of the precompile must be uploaded as binary to OS/390 and placed in the DBRM library. You can then use a DB2 Bind to create a Plan from the DBRM.
Notes:
a. If your application uses dynamic SQL, include DBRM ABTBASE as input to the bind of your plan. This file is delivered as abtbase.dbr in your IMAGE subdirectory. You need to upload, as binary, the abtbase.dbr file to a DBRM library where you can use it as input to your binds for DB2 for OS/390.
b. If you receive bind error DSNT231I, ensure that the DBRM name you gave to the SQL package is exactly the same as the member name containing the DBRM on input to the bind.
Step 7: Packaging and running your application
When you package an image to run on OS/390, you can package the image as a single module or as a dependent module. A dependent module uses the classes of a shared base module to run. See the Server Guide for the steps to package your application image.
If your application uses .mpr files, you will need to select the Dumper policy type and the Make Message Catalogs Resident option during the Modify Instructions step. See Step 2: Separating strings in your application.
If you are writing an application that will work with CICS, see Server Guide for more information about how to run the packaged image. If you are running in CICS Transaction Server 3.3 and you receive the CICS abend ASRA, ensure that the taskdataloc is specified as BELOW in the transaction definition of your program. There is a description of the proper definition for a transaction in the Server Guide.
If you are writing an application that will work with IMS, see the Server Guide for more information about how to run the packaged image.
If you are writing an application that will work with OS/390, see the Server Guide for more information about how to run the packaged image.
Last modified date: 07/25/2020