Go to Home Page
Questions?
Call 1-800-572-5517
 
  Go to Home Page  
  See all products
  See price schedules
  See manuals, tutorials, articles
  Download a free 30-day trial
  See user testimonials
  About Pacific Systems Group
 
  Welcome Rocket Software MXI Users - Learn about Spectrum SMF Writer for MXI
  Choose Spectrum Writer to add 4GL to your product
  Report Writer Speedup Tips Article

Return to list of articles

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.

Using Spectrum Writer with DB2 Databases

Spectrum Writer's DB2 Option lets you use DB2 data with Spectrum Writer exactly like you use other mainframe data. That means you can:

  • 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.
  • use DB2 data to create Web reports. Spectrum Writer's DB2 Option has these features:
  • 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朌B2 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.

It's easy to use DB2 data with Spectrum Writer. You use the same control statements that you already know, with just a few differences. In fact, the only statements affected by the DB2 Option are these:

  • the OPTIONS statement
  • the INPUT statement
  • the READ statement (not required)
  • the FILE statement (not required)

For most reports and PC files, you抣l 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杢ime modules are located. An example of a DB2 run杢ime 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.

Using DB2 Data in Reports

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杛elated 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.

Next notice the INPUT statement. There are two names used in the INPUT statement:

  • PROJECT, which is a user朼ssigned "Spectrum Writer name" for this input file. You can put any name here that you like. This name is not known to DB2 at all. In most runs, this name will never be referred to again. (However, in runs that use multiple input files, as you'll see later, "PROJECT" would be used to refer specifically to this input file.)
  • 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.

  • it associates an actual DB2 table with a user杅riendly Spectrum Writer "file name." (This association is not permanent枛 it lasts only during the one Spectrum Writer run.)
  • it makes that DB2 table the primary input for your Spectrum Writer run. These are the only required parms for an INPUT statement for a DB2 table. Subsequent sections of this chapter discuss other, optional, DB2-related parms for the INPUT statement. (The complete syntax for the INPUT statement appears on page 542.)

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朌B2 input file.

That's all there is to using DB2 data with Spectrum Writer! Here's a review of the differences from non朌B2 Spectrum Writer requests:

  • no data definition of your DB2 file is necessary (that is, no FILE or FIELD statements are required)
  • no Spectrum Writer Copy Library is required
  • use an OPTIONS statement with the DB2SUBSYS parm (to identify the DB2 subsystem to use)
  • use the DB2NAME parm in your INPUT statement (to identify the primary DB2 table to use)

Note: Spectrum Writer supports character, numeric, date and time fields from DB2 tables. DB2 "timestamps" are treated as 26朾yte character fields by Spectrum Writer. DB2 "graphic strings" and "floating point" numbers are not supported.

Using DB2 Data in PC Programs

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𣇽 spreadsheet is shown in Figure 64. This example shows the same "project table" data being used in a Lotus 1𣇽 spreadsheet.

What Fields Are in Your DB2 Table?

You may not remember the names of all of the fields defined for your DB2 table. Spectrum Writer will list the DB2 fields available in your DB2 file for you. Just use the SHOWFLDS(YES) parm in your INPUT statement:

     INPUT: PROJECT
              DB2NAME('DSN8230.PROJ')
              SHOWFLDS(YES)

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.

Using the WHERE Parm

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.

Now let's consider a more advanced report. What if we want to include only the records for department D21 in our report. Of course, the standard way to do that with Spectrum Writer is to use an INCLUDEIF statement, like this:

INCLUDEIF: DEPTNO = 'D21'

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".

But when using DB2 data as your input, there is another way to accomplish the same thing. You can let DB2 do the record selection rather than Spectrum Writer. To do this, use a WHERE parm in the INPUT statement:

     INPUT: PROJECT
 	     DB2NAME('DSN8230.PROJ')
 	     WHERE(DEPTNO = 'D21')

The WHERE parm in the INPUT statement serves the same function as the WHERE clause in a DB2 "SELECT" statement. It tells DB2 which rows we want from the DB2 table. If your INPUT statement contains a WHERE parm, Spectrum Writer will include it as a WHERE clause in the SELECT statement that it builds for DB2. (If your INPUT statement does not have a WHERE parm, the SELECT statement will not have a WHERE clause, and DB2 will return all rows from the DB2 table.)

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杛unning 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.

Using the ORDERBY Parm

Another optional parm in the INPUT statement is the ORDERBY parm. (Note that this parm must be spelled with no imbedded space.)

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.

Then DB2 will pass Spectrum Writer the rows in an "arbitrary" order.)

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 example in Figure 65 uses an ORDERBY parm in the INPUT statement. Within the ORDERBY parm, you may list one or more DB2 fields, along with the optional keywords ASC and DESC (for "ascending" and "descending.") Here are two examples of INPUT statements that use the ORDERBY parm:

     INPUT: PROJECT
              DB2NAME('DSN8230.PROJ')
              ORDERBY(DEPTNO, PROJNAME)

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.

     INPUT: PROJECT
              DB2NAME('DSN8230.PROJ')
              WHERE(DEPTNO = 'D21')
              ORDERBY(PROJNAME DESC)

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.

Using Multiple DB2 Tables

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.

Let's begin by reviewing how the READ statement works with VSAM files. The file named in the INPUT statement is called the "primary input file." Spectrum Writer always reads this primary input file sequentially. Then, each time a record is read from the primary file,

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.

You can also use READ statements with DB2 tables. Each READ statement will cause one row of data to be read from a DB2 table (or multiple rows if the MULTI parm is used). Instead of using a READKEY parm, use the WHERE parm to identify which row(s) you want to read. (Please refer to "Using the WHERE Parm" on page 397. The WHERE parm抯 syntax is discussed in "WHERE Parm Syntax" on page 405.)

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.

     INPUT: PROJECT
              DB2NAME('DSN8230.PROJ')
     READ:  EMPLOYEE
              DB2NAME('DSN8230.EMP')
              WHERE(EMPNO = RESPEMP)

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.

You can also use the ORDERBY parm in the READ statement. As mentioned, by default Spectrum Writer fetches only a single row from a READ file (for each row retrieved from the INPUT file). It is possible that the WHERE clause will not uniquely identify a single row in the READ file. In that case, you can use the ORDERBY parm to determine which row DB2 will return first to Spectrum Writer. For example, if there were more than one employee with the same employee number in the employee table, you might specify:

     READ: EMPLOYEE
	     DB2NAME('DSN8230.EMP')
              WHERE(EMPNO = RESPEMP)
              ORDERBY(LASTNAME)

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.

One-to-Many Table Matching

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杢o朚any" 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.

     INPUT: PROJECT
              DB2NAME('DSN8230.PROJ')
     READ:  EMPLOYEE
	     DB2NAME('DSN8230.EMP')
              WHERE(EMPNO = RESPEMP)
     READ:  DEPARTMENT
              DB2NAME('DSN8230.DEPT')
              WHERE(DEPARTMENT.DEPTNO = PROJECT.DEPTNO)

Notice the READ statement on the previous page. In its WHERE parm we had to use record name prefixes to uniquely identify the DEPTNO fields. If we had written DEPTNO by itself, it would have resulted in an "ambiguous field name" error. That's because a field named DEPTNO exists in the project table and in the department table. We prefixed each occurrence of DEPTNO with a record name to eliminate the ambiguity. The WHERE parm correctly identifies the row that we want to read from the department file. It is the row whose own DEPTNO field equals the DEPTNO field from the project table. (The use of record names is discussed further in "WHERE Parm Syntax" on page 405.)

The report in Figure 67 uses the three statements above.

Where Parm Syntax

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.

The main differences in syntax concern:

  • Record Name Prefixes: Spectrum Writer allows you to prefix any field name in the WHERE parm with a Spectrum Writer record name (to eliminate possible ambiguity)
  • Date and Time Literals: you may use either Spectrum Writer's own date and time literals, or DB2's date and time literals In a DB2 WHERE clause, each operand in a comparison can be any of the following:
  • the name of a DB2 column in the table
  • the name of a "host variable" (in DB2 terminology)
  • a literal value

Spectrum Writer also supports all 3 kinds of operands in the WHERE parm. Here is a short discussion of each type of operand.

DB2 columns

Your comparisons can refer to any DB2 column in the "current" DB2 table. (That is, the DB2 table named in the DB2NAME parm of the same statement.) For example:

     READ: PROJECT
             DB2NAME('DSN8230.PROJ)
             WHERE(DEPTNO = 'D21')

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.

To avoid such ambiguity, Spectrum Writer allows you to prefix any field name within the WHERE parm with a record name. (For more information on record names, see "How to Name the Input File Records" on page 228. Briefly, each input record has a record name. This record name can be specified explicitly with the RECNAME parm of the INPUT and READ statements. If no RECNAME is specified, the record name is the same as the file name.) To tell Spectrum Writer that we mean the DEPTNO field from the "current" DB2 table, we would write:

     READ: PROJECT
             DB2NAME('DSN8230.PROJ)
             WHERE(PROJECT.DEPTNO = 'D21')

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.

Host Variables

When a field name in a WHERE parm refers to a field that is not in the current DB2 table, that field must be passed to DB2 as a "host variable." Spectrum Writer takes care of this for you automatically. It substitutes a "host variable marker" in the WHERE clause that is passed to DB2. Consider the following statements:

     COMPUTE: TEST朌EPT = TEST朙ETTER + '21'
     READ: PROJECT
             DB2NAME('DSN8230.PROJ)
             WHERE(DEPTNO = TEST朌EPT)

In this example, we have created a COMPUTE field named TEST朌EPT. In the WHERE parm, DEPTNO is compared to this COMPUTE field. In this case, Spectrum Writer would recognize that TEST朌EPT is not a field within the project DB2 table. So, it substitutes a host variable marker for TEST朌EPT before passing the WHERE clause to DB2. Doing this provides DB2 access to Spectrum Writer's internal value for the COMPUTE field (TEST朌EPT).

Once again, if a host variable name is not unique, you may prefix it with a record name to make it unique.

There is an example of a host variable in the report in Figure 67. Notice the READ statement for the employee DB2 table. It looks like this:

     READ: EMPLOYEE
             DB2NAME('DSN8230.EMP)
             WHERE(EMPNO = RESPEMP)

EMPNO is a field within the current (employee) table. But Spectrum Writer treats RESPEMP as a host variable, since it is not a field within the employee table. (RESPEMP is a field from an earlier DB2 table枛 the project table.)

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.

Literals

Your WHERE parm expression can contain any valid DB2 literal. In addition, you are allowed to use Spectrum Writer's own literal formats. For example, if you wanted to, you could use a date literal in DB2's ISO date format, like this:

     READ: PROJECT
             DB2NAME('DSN8230.PROJ)
             WHERE(PRSTDATE = '19930131')

Or, you could use a Spectrum Writer date literal, like this:

     READ: PROJECT
             DB2NAME('DSN8230.PROJ)
             WHERE(PRSTDATE = 1/31/1993)

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.

Note: Floating point literals are not allowed. For simplicity, the examples in this discussion have shown only a single test in the WHERE parm. However, you are allowed to specify as many tests as you like in your WHERE parm. For example:

     READ: PROJECT
            DB2NAME('DSN8230.PROJ)
            WHERE(PRSTDATE <= 1/31/1993 AND (DEPTNO = 'D21' OR DEPTNO = 'E11'))

Customizing Your DB2 Fields

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.

Since FIELD statements are not supported for DB2 fields, how do you permanently define such things as:

  • the column headings to use for a field
  • the display format to use for a field
  • whether or not a numeric field should be totalled in reports

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).

For example, let's pretend that our project DB2 table contains a column named PROJTEL, which is a telephone number stored in DB2's "integer" format. By default Spectrum Writer would treat it as a regular numeric field, which means it would be formatted with commas, it would be totalled, etc. Of course, for a particular run you could change these defaults directly in your COLUMNS statement, like this:

COLUMNS: PROJTEL(PIC'(999) 9999999', NOACCUM)

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.

But if you will be using a field in many different reports, it would be easier to specify the display format and the NOACCUM parm just once and then forget about them. Do that by using a COMPUTE statement, like this:

COMPUTE: TELEPHONE(PIC'(999) 9999999', NOACCUM) = PROJTEL

Now, whenever the field TELEPHONE is used in a report, it will be formatted appropriately, and will not be totalled. You can use the same method to define column headings for a DB2 field:

COMPUTE: TELEPHONE(PIC'(999) 9999999', NOACCUM, 'TEL#') = PROJTEL

Now TELEPHONE will have TEL# as its default column heading in reports and PC files.

Saving DB2 File Definitions

The previous section explained how to use COMPUTE statements to customize your DB2 fields. A convenient way to handle these COMPUTE fields is to store them in your Spectrum Writer Copy Library. (See "Keeping Your File Definitions in a Copy Library" on page 360 for detailed information on using the copy library.)

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.

For example, for the project DB2 table you might create a member named PROJECT in the copy library. It might contain these statements:

     FILE: PROJECT DB2NAME('DSN8230.PROJ')
     COMPUTE: TELEPHONE(PIC'(999) 9999999', NOACCUM, 'TEL#') = PROJTEL
     COMPUTE: NUMBER('PROJECT NUMBER') = PROJNO
     COMPUTE: NAME('PROJECT NAME') = PROJNAME
     COMPUTE: SHORT朠ROJ朜AME = #SUBSTR(PROJNAME,1,5)
     COMPUTE: YEARLY朣TAFF(PIC'ZZZ9') = PRSTAFF * 52

Now we could request reports or PC files from the project DB2 table as easily as this:

     INPUT: PROJECT
     COLUMNS: NUMBER SHORT朠ROJ朜AME TELEPHONE PRSTAFF YEARLY朣TAFF

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朌B2 files from your end杣sers point of view. A programmer can do the small amount of setup required. Then end杣sers can use DB2 data in Spectrum Writer without necessarily even knowing it comes from a DB2 table.

DB2 Setup

Before you use Spectrum Writer with DB2 data for the first time, some simple DB2 setup is required. (You will also need to perform this setup each time you install a new release level of Spectrum Writer.) In most shops, this DB2 setup is performed by a Database Administrator. The setup consists of these two steps:

  • 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.

1. Creating the DB2 Plan
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.

Note: It is possible to use a different plan name if that is necessary for some reason. But you will then have to tell Spectrum Writer the name of your plan in every job you run. That is done with the DB2PLAN option:

OPTION: DB2PLAN('OURNAME')

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.

2. Granting DB2 Execute Authority
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 Restrictions

DB2 has certain restrictions which Spectrum Writer must observe. In particular, you should keep the following restriction in mind:

  • 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.
Copyright 2017.
Pacific Systems Group.
All rights reserved.


Spectrum Writer 4GL - the economical alternative to SAS, Easytrieve, DYL-280...

Home | Products | Prices | Documentation | 30-Day Trials | Customer Reviews | Company
Send Your Comments or Questions