This excerpt from the Spectrum Writer Reference Manual provides some tips that you may find helpful when working with DB2 tables. Return to article index.
produce attractive custom reports from DB2 tables in just minutes.
turn DB2 data into PC files designed especially for PC spreadsheet, database and graphics programs.
turn DB2 data into any custom file format you need for use on mainframes, Unix machines, database servers, etc.
no data dictionary is required when using DB2 data. You just use the standard DB2 names for your DB2 tables, views, and columns. This means you can start using Spectrum Writer with all of your DB2 tables right away.
you can combine data from up to 15 different DB2 tables to create a single report or PC file.
you can even mix DB2 data with data from non�DB2 files. For example, you might have a tape file as the primary input to a Spectrum Writer job. Using data from that file, you could read additional data from VSAM files and/or DB2 tables. Or, you could use a DB2 table as your primary input and use data from it to read from additional DB2 tables or VSAM files. The possibilities are endless.
For most reports and PC files, you�ll only use the OPTIONS and INPUT statements.
JCL Note: When using DB2 tables with Spectrum Writer, be sure that the STEPLIB DD in the execution JCL points to the load module where DB2's run�time modules are located. An example of a DB2 run�time module is DSNTIAR.
In the following sections, we assume that you are already familiar with using Spectrum Writer to request reports and output files. These sections explain the few differences that you need to know in order to use DB2 data in Spectrum Writer.
Let's begin by looking at an actual Spectrum Writer report that uses DB2 data. Notice the sample report in Figure 63. Two of the control statements in this example contain DB2�related information. They are the OPTIONS statement and the INPUT statement.
First notice the OPTIONS statement. You'll see that we used the DB2SUBSYS option. This option tells Spectrum Writer which DB2 subsystem to access. Many shops have multiple DB2 subsystems. For example, a shop might have a test subsystem and a production subsystem. This option tells Spectrum Writer which subsystem to access for a particular run.
In our example, we specified a DB2 subsystem named "DB2T." That's the test subsystem in our "imaginary" company.
The DB2SUBSYS option is required when using DB2 data in a run. Remember to specify this option before your INPUT statement.
DSN8230.PROJ, which is of course the actual name of the DB2 table. You can name a DB2 table or a DB2 view in this parm. By the way, DSN8230.PROJ is a real "sample table" that was supplied by IBM with the DB2 system. Therefore, you can run this same job in your own shop for practice, if you like. This table contains information about various projects in an imaginary company.
The INPUT statement does two things.
Terminology: For the sake of consistency, we'll refer to the DB2 table named in an INPUT statement as an "input file," even though technically speaking it is not a "file". Similarly, we'll refer to DB2 columns as "DB2 fields" in this manual.
After your INPUT statement, you can use any of the other Spectrum Writer statements in any way you like. Refer to the DB2 fields by using their standard, unqualified DB2 names. Spectrum Writer will automatically recognize these DB2 names. For example, in the COLUMNS statement in Figure 63 above, we referred to the following DB2 fields from the project table: PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE and PRSTAFF.
You can also use the DB2 fields in the SORT statement, COMPUTE statements, INCLUDEIF statements, BREAK statements, and all the other Spectrum Writer statements. Just use the DB2 fields in exactly the same way as you would use the fields from a non�DB2 input file.
Note: Spectrum Writer supports character, numeric, date and time fields from DB2 tables. DB2 "timestamps" are treated as 26�byte character fields by Spectrum Writer. DB2 "graphic strings" and "floating point" numbers are not supported.
We've just seen how easy it is to use DB2 data in custom reports with Spectrum Writer. It's just as easy to turn your DB2 data into PC files with Spectrum Writer. Simply add the appropriate PC option to the OPTIONS statement. An example of using DB2 data in a Lotus 1�2�3 spreadsheet is shown in Figure 64. This example shows the same "project table" data being used in a Lotus 1�2�3 spreadsheet.
What Fields Are in Your DB2 Table?
The above statement causes a list to be printed showing each DB2 field available from the DSN8230.PROJ table. This list appears in the Spectrum Writer control statement listing. The list also indicates the data type (character, numeric, date or time) of each of the DB2 fields.
The SHOWFLDS parm can also be used in the READ statement.
Here's how Spectrum Writer interacted with the DB2 subsystem in order to produce the report in Figure 63. Spectrum Writer first opened a "cursor" with DB2 that "selected" the DB2 fields needed to produce the report. It then "fetched" from DB2 all the rows for that cursor. Since no INCLUDEIF statement was used, Spectrum Writer included in the report all the rows that were returned by DB2.
And that method works just fine! If you use this statement, Spectrum Writer would again fetch all rows from the DB2 table. Spectrum Writer would then examine the DEPTNO field in each row and include in the report only those rows where the DEPTNO field contained "D21".
In the example above, the WHERE parm causes DB2 to return to Spectrum Writer only those rows from the project table whose DEPTNO field equals "D21". If you used this WHERE parm, you would not need an INCLUDEIF statement. You would want Spectrum Writer to include all the rows that DB2 returned to it.
As far as the final report goes, using the WHERE parm yields identical results to using the INCLUDEIF statement. Feel free to use whichever method you're most comfortable with. The example in Figure 65 uses a WHERE parm in the INPUT statement.
Performance Note: Which one of these methods is more efficient? There is no "right" answer for all cases. It depends on various factors, including the percentage of records that are included in the report. For long�running jobs, where performance is an important consideration, you may want to try running the job each way and choose the method that works best in your particular case.
You can also use a combination of the WHERE parm and the INCLUDEIF statement. If you do, DB2 will pass to Spectrum Writer all rows that meet the WHERE conditions. Of those rows, Spectrum Writer will then include in the report only the ones that meet the INCLUDEIF statement conditions.
See "WHERE Parm Syntax" for further details about the syntax allowed in the WHERE parm.
The ORDERBY parm in Spectrum Writer serves the same function as the ORDER BY clause in a DB2 "SELECT" statement. It tells DB2 what order to pass the rows to Spectrum Writer in. If your INPUT statement contains an ORDERBY parm, Spectrum Writer will include it as an ORDER BY clause in the SELECT statement that it builds for DB2. (If your INPUT statement does not have a ORDERBY parm, the SELECT statement will not have an ORDER BY clause.
Use this parm if you want DB2 to pass its rows to Spectrum Writer in a certain order. You may wish to use this parm rather than using a SORT statement. When no SORT statement is used, Spectrum Writer outputs the data in the same order that DB2 passes it to Spectrum Writer in.
The above example would cause DB2 to return the rows from the project table to Spectrum Writer in department number order, with "ties" being further sorted in project name order.
The above statement would cause the rows from the project table to be returned to Spectrum Writer in descending project name order. As you can see, you are allowed to use both the WHERE and ORDERBY parms, if you wish. Their order in the INPUT statement is not important.
Note: If you want one or more control breaks in your report, you should use the SORT statement (rather than the ORDERBY parm). That is because Spectrum Writer only allows control breaks on fields that are in a SORT statement.
Note: You can use both an ORDERBY parm and a SORT statement, though this would rarely be useful. DB2 would pass the rows from the DB2 table to Spectrum Writer in the order specified in the ORDERBY parm. Spectrum Writer would then sort the final report according to the SORT statement.
Sometimes the DB2 table in your INPUT statement will not contain all the data you need for a report or a PC file. In that case, you can use one or more READ statements to obtain data from additional DB2 tables.
Spectrum Writer reads one additional record from each VSAM file named in a READ statement. The READKEY parm (in the READ statement) tells Spectrum Writer what key to use when performing the read. The key is usually a field from the primary input file.
Let's start with the DB2 report on page 394 to illustrate the use of the READ statement. That report shows data from the "project" DB2 table. One of the items in the project table is called RESPEMP. This is the employee number of the project's "responsible employee." Now suppose we want to include the employee's actual name in our report. The employee name is not kept in the project table. But it is kept in a different DB2 table �� the employee table.
We can use the following statements to get data from both the project and the employee tables for use in our report.
Notice that the READ statement, like the INPUT statement, begins with a Spectrum Writer file name. It also has the DB2NAME parm. And, unlike the INPUT statement, the WHERE parm is required in a READ statement.
Here's how Spectrum Writer will process the above statements. The primary input to the report is the project DB2 table. So, Spectrum Writer will retrieve all rows from the DB2 project table. For each row from the project table, Spectrum Writer will now also fetch a single row from the employee table. The row from the employee table will be the row whose EMPNO field equals the RESPEMP field from the project table.
As a result of these two statements, you now have access to every DB2 field in both the project and the employee DB2 tables. You can use those DB2 fields in your COLUMNS statement, SORT statement, COMPUTE statements, and so on. This simple way of linking multiple DB2 table is one of Spectrum Writer's most powerful features. All it takes is a single READ statement.
The report in Figure 66 illustrates this example. Our report now includes LASTNAME, which is a column from the employee DB2 table. This report shows the last name of the employee responsible for each project.
The above statement specifies that DB2 should return rows from the employee table in LASTNAME order. Therefore, if multiple rows existed for a certain employee number, DB2 would return the row whose LASTNAME came first alphabetically. If no ORDERBY parm is specified and multiple rows meet the WHERE condition, DB2 will return the rows in an "arbitrary" order. When processing READ statements, Spectrum Writer always uses the first row returned by DB2.
Note: For simplicity's sake, in this discussion we implied that Spectrum Writer always reads a row from each READ file. In some cases, Spectrum Writer may be able to detect that data from an auxiliary input table will not actually be needed in the run and, to improve performance, will not perform the read.
The complete READ statement syntax is shown on page 578.
If you want to use all of the rows that meet the WHERE parm conditions, add the MULTI parm to your READ statement. When the READ statement has the MULTI parm, Spectrum Writer creates and processes "logical input records" by matching the primary input file row with each qualifying row from the auxiliary input file. For more information on how the MULTI parm works, see "How to Perform "One�to�Many" Reads" on page 232.
In the previous example, we showed how to use a READ statement to obtain data from a second DB2 table. But you're not limited to using only two DB2 tables at a time. Spectrum Writer allows you to use up to 15 different DB2 tables in a single run.
In this section, we'll show another example of using multiple DB2 tables in a single run. This time, we'll use two READ statements. That will give us access to the data from three DB2 tables altogether.
Let's pick up with the report we just produced in Figure 66. That report contains data from the project DB2 table. It also shows the "responsible employee's" last name, which comes from the employee DB2 table. Now suppose we want to show the department name for each project (not just the department number). Another DB2 table, called the department table, contains the names of each department. We'll read a row from that table in order to get the department name.
The report in Figure 67 uses the three statements above.
The syntax allowed within the WHERE parm is similar to, but not identical to, the DB2 syntax for a WHERE clause (in the DB2 "SELECT" statement). This section discusses the differences from the DB2 syntax.
Spectrum Writer also supports all 3 kinds of operands in the WHERE parm. Here is a short discussion of each type of operand.
In the WHERE parm above, DEPTNO is the name of a DB2 column within the DSN8230.PROJ table. This WHERE parm would select all rows from the project table where the DEPTNO field is equal to the literal value 'D21'.
In this example, the Spectrum Writer WHERE parm syntax is identical to the DB2 WHERE clause's syntax. But a problem can arise if the DB2 column name is not unique. This happens when an earlier input file contains a field by the same name. It can also happen if you create a COMPUTE field with the same name as a DB2 column.
Let's assume that our primary input file also has a field named DEPTNO in it. In that case, the WHERE parm above would result in an "ambiguous field name" error. Spectrum Writer wouldn't know whether you were referring to the DEPTNO field in the primary input file, or the DEPTNO field in the current (PROJECT) DB2 table.
In the above statement, we used the record name of the "current" table (PROJECT) to prefix the DB2 field name. Now Spectrum Writer knows that the DEPTNO operand refers to the DB2 column within the project table itself, and not to the DEPTNO field from the primary input file.
Note: Don't confuse Spectrum Writer's record name prefix with a DB2 qualifier. DB2 qualifiers are not necessary and are not allowed within Spectrum Writer's WHERE parm.
Note: Some COMPUTE fields are not associated with any input record, and therefore cannot be prefixed with a record name. If you have problems with ambiguous field names due to such a COMPUTE field, the solution may be to choose a different name for your COMPUTE field.
In this example, we have created a COMPUTE field named TEST�DEPT. In the WHERE parm, DEPTNO is compared to this COMPUTE field. In this case, Spectrum Writer would recognize that TEST�DEPT is not a field within the project DB2 table. So, it substitutes a host variable marker for TEST�DEPT before passing the WHERE clause to DB2. Doing this provides DB2 access to Spectrum Writer's internal value for the COMPUTE field (TEST�DEPT).
Once again, if a host variable name is not unique, you may prefix it with a record name to make it unique.
Note: Do not use a colon (:) to indicate a "host variable" within the WHERE parm (as you would when writing SQL code). As explained above, Spectrum Writer examines each field name in your WHERE parm and determines whether it is the name of a DB2 column within the current table or not. Spectrum Writer automatically takes care of passing host variables to DB2 for you.
Either format will yield the same result. When you use DB2 format literals, Spectrum Writer's passes them in the WHERE clause to DB2 unchanged. When you use a Spectrum Writer literal, Spectrum Writer passes it as a "host variable" to DB2. Note that for character and numeric literals, the formats are the same for DB2 and for Spectrum Writer. So your choice in choosing literals applies only to date and time literals.
As mentioned earlier, no FILE or FIELD statements are needed to define the fields in a DB2 input file. Spectrum Writer recognizes the actual DB2 column names that are defined for your DB2 table.
You can use COMPUTE statements to perform such customization. Use a COMPUTE statement that simply assigns the value of a DB2 field to the COMPUTE field. The COMPUTE statement syntax supports column headings, display formats and the ACCUM/NOACCUM parms (which determine whether a field is totalled or not).
In the above statement we specified an override display format (a "picture"), to make the numeric value look like a telephone number. And we specified NOACCUM to prevent the column from being totalled at the end of the report.
Now TELEPHONE will have TEL# as its default column heading in reports and PC files.
Briefly, here's what to do. Create a member in the copy library for the DB2 file you want to define. In that member, put a FILE statement that specifies the desired filename and its DB2 name. Then add one COMPUTE statement for each DB2 field that you wish to customize. You might also want to include COMPUTE statement for any commonly used computations involving the DB2 fields. Do not put any FIELD statements in this member. FIELD statements are not allowed for DB2 files.
Upon seeing the INPUT statement for PROJECT, Spectrum Writer would process the FILE and COMPUTE statements from the PROJECT member in the copy library. Since the FILE statement contains the DB2NAME parm for PROJECT, the INPUT statement doesn't need it.
The COLUMNS (and any other) statements can now refer to either the actual DB2 field name, or the COMPUTE fields that we defined. Using the COMPUTE field names results in the column headings and display formats that were specified for those fields.
This method makes DB2 files look and work just the same as non�DB2 files from your end�users point of view. A programmer can do the small amount of setup required. Then end�users can use DB2 data in Spectrum Writer without necessarily even knowing it comes from a DB2 table.
a new DB2 "plan" must be created and "bound." This plan identifies Spectrum Writer to your DB2 system.
authority to execute this plan must then be "granted" to your users.
Note: If Spectrum Writer will be used on multiple DB2 subsystems, these steps should be performed on each of those subsystems.
The first step is to create a new DB2 plan. The plan name should be "SPECTnnn", where nnn is the release level of Spectrum Writer. For example, the plan name for release 3.0.0 of Spectrum Writer is SPECT300. That is the plan name that Spectrum Writer assumes you will use.
If you use "SPECTnnn" as your plan name, you will not need to use the above statement.
After creating the SPECTnnn plan, you must "bind" two Spectrum Writer "DBRM" modules into that plan. You can perform the bind with ISPF, or any other way your shops prefers.
Note: The DBRM modules were included with your original installation files.
After you have created and bound the DB2 plan, you must grant "execute authority" for that plan. Generally you will grant execute authority for this plan to PUBLIC. That allows anyone in your shop to execute Spectrum Writer. But it does not mean that every user can now access every DB2 table in the shop! Each user's access will still be limited to those DB2 tables that they have been granted access to. Granting them execute authority on "SPECTnnn" simply allows them to execute the Spectrum Writer program with its DB2 Option.
Here's how a user's access is determined. Each Spectrum Writer job has a DB2 "authorization ID" that is (or is related to) the jobname used for the run. If a Spectrum Writer job tries to access a DB2 table which is not permitted for that jobname, DB2 will return an error message to Spectrum Writer. Spectrum Writer will not be able to access that particular table, and will print an error message to that effect. If the jobname does have authority to read the DB2 table, Spectrum Writer will then access the DB2 data and complete the run normally.
DB2 allows a maximum precision of 15 digits in numeric operands. Any decimal digits also count toward this maximum of 15 digits. (Spectrum Writer allows a precision of 31 digits.) This means, for example, that any Spectrum Writer COMPUTE field that you refer to in a WHERE clause must never have a value smaller than �999999999999999 or greater than +999999999999999. And, if the field contains decimal digits, the allowed range of values is reduced even further.