View figure.The DB2 Server for VSE & VM messages and HELP texts are stored in tables, meaning that they can be retrieved and manipulated just like any other data. You can modify the information to suit local needs in the following ways:
| Note: | A HELP command causes ISQL to issue a SELECT statement on the tables. |
Figure 27 shows the relationships between the tables used by the application server for HELP text support.
Figure 27. Relationships between SYSLANGUAGE, SYSTEXT1, and SYSTEXT2
| View figure. |
The relationships between the tables are maintained through the following sets of matching columns:
These tables are explained in more detail below.
HELP and messages texts are provided in several national languages. During installation, one language is established as the default; it can be changed after installation. In addition, you can make more than one language available to ISQL users.
The SYSLANGUAGE table is created as part of the installation process. It lists all national languages that are currently supported on the application server, meaning that both HELP text and messages text are available in these languages. Its primary purpose is for use with the message repository, which is a mandatory part of the product installation. Installing the HELP text is optional.
Each entry in this table has the following fields:
To view all the columns of the SYSLANGUAGE table, enter the following query:
SELECT * FROM SQLDBA.SYSLANGUAGE
Figure 28 shows a sample SYSLANGUAGE table.
Figure 28. Sample SQLDBA.SYSLANGUAGE Table
LANGUAGE LANGKEY REMARKS LANGID -------------- ------- --------------------------------------- -------- ENGLISH S001 AMERICAN ENGLISH VERSION OF HELP TEXT AMENG ENGLISH S002 ENGLISH UPPER CASE VERSION OF HELP TEXT UCENG FRENCH S003 FRENCH VERSION OF HELP TEXT FRANC FRANCAIS S003 TEXTE D'AIDE FRANCAIS FRANC |
The language key (LANGKEY) can be one of those listed in Table 26.
| Language Key | Description | Language ID |
|---|---|---|
| S001 | American mixed case | AMENG |
| S002 | English upper case | UCENG |
| S003 | French | FRANC |
| S004 | German | GER |
| D001 | Japanese | KANJI |
| D003 | Simplified Chinese | HANZI |
| Note: | IBM has reserved the following language key ranges for use by future languages supplied by IBM: |
In VM, the default language is established by the language currently set in CMS. If this language is not supported by the application server, then the default language defined during installation is used.
In VSE, the default language is established by the following:
The HELP text tables are normally loaded during the installation process. The DBS Utility accomplishes this task by creating the HELP text tables SYSTEXT1 and SYSTEXT2 for the user SQLDBA, loading data into both tables (through DATALOAD), and creating an index on each.
Figure 29 shows the formats of these tables, but not the actual tables.
Figure 29. Formats of the Tables SYSTEXT1 and SYSTEXT2
SYSTEXT1 SYSTEXT2
TOPIC ITEM ITEM SEQNO "SQL/DS HELP" LANGKEY
----------- ---- ---- ----- ----------------------- -------
VIEW 5260 5260 10 TOPIC NAME: CREATE VIEW S001
VIEW 5330 * * * *
VIEW 5920 5260 110 CREATE VIEW is an SQL ... S001
VIEWS 5260 5260 120 more tables. You can ... S001
VIEWS 5330 * * * *
VIEWS 5920 * * * *
* * 5260 1070 DELIVERY_TIME was less ... S001
CREATE VIEW 5260 5260 1080 S001
* 5260 10 RUBRIQUE : CREATE VIEW S003
DROP VIEW 5330 * * * *
* * * * * *
* * 5260 100 CREATE VIEW est une ... S003
* * 5260 110 d'une ou plusieurs ... S003
* * * *
* * * *
5260 1110 FAB, ART et JOURS) ... S003
5260 1120 S003
5330 10 TOPIC NAME: DROP VIEW S001
* * * *
* * * *
5330 90 DROP VIEW is an SQL ... S001
5330 100 SQL/DS also automatically ... S001
* * * *
* * * *
|
The following SQL statements are used during the loading process to create SYSTEXT1 and SYSTEXT2:
CREATE TABLE SQLDBA.SYSTEXT1 (TOPIC CHAR(20) FOR BIT DATA NOT NULL,
ITEM SMALLINT NOT NULL)
IN "PUBLIC"."HELPTEXT"
CREATE TABLE SQLDBA.SYSTEXT2 (ITEM SMALLINT NOT NULL,
SEQNO SMALLINT NOT NULL,
"SQL/DS HELP" CHAR(60) FOR BIT DATA NOT NULL,
LANGKEY CHAR(4) NOT NULL)
IN "PUBLIC"."HELPTEXT"
When a user enters a HELP command, a query like this is processed:
SELECT "SQL/DS HELP"
FROM SQLDBA.SYSTEXT1, SQLDBA.SYSTEXT2
WHERE TOPIC = 'topicname'
AND SQLDBA.SYSTEXT1.ITEM = SQLDBA.SYSTEXT2.ITEM
AND LANGKEY = 'XXXX'
where XXXX is the four-character language key that indicates a specific HELP text language from among those currently installed on the DB2 Server for VSE & VM application server.
When the support for languages is installed, HELP text may or may not be available depending on your site's requirements. Each ISQL user can select from among the languages currently installed on the application server. To view which languages are currently installed, a user enters the following query:
SELECT LANGUAGE FROM SQLDBA.SYSLANGUAGE
The user can then change the default language with the ISQL SET LANGUAGE command.
The topic that the user supplies is substituted in topicname. An ORDER BY clause is not used in the query because these indexes are defined on the tables:
CREATE INDEX SQLDBA.SYSTEXT1INDEX
ON SQLDBA.SYSTEXT1(TOPIC,ITEM)
CREATE INDEX SQLDBA.SYSTEXT2INDEX
ON SQLDBA.SYSTEXT2(ITEM,SEQNO,LANGKEY)
CREATE INDEX SQLDBA.SYSLANGINDEX
ON SQLDBA.SYSLANGUAGE(LANGUAGE)
CREATE INDEX SQLDBA.SYSLANGINDEX
ON SQLDBA.SYSLANGUAGE(LANGID)
A HELP command uses SYSTEXT1 as a pointer to SYSTEXT2. Suppose an ISQL user enters:
help 'view'
The parameter 'view' is converted to uppercase. The database manager finds all occurrences of the character string 'VIEW' in the TOPIC column of SYSTEXT1 for the HELP text of the current language. See Figure 30.
Figure 30. Use of the SYSTEXT1 Table
SYSTEXT1
TOPIC ITEM
----------- ----
-------> VIEW 5260
-------> VIEW 5330
-------> VIEW 5920
CREATE VIEW 5260
CREATE V 5260
DROP VIEW 5330
VIEW QUERY 5030
VIEW MODS 5040
* *
* *
* *
|
Figure 30 shows three occurrences of the string VIEW. Each has an item number associated with it (5260, 5330, 5920). These numbers and the language key are used as pointers (through the query join) to the ITEM numbers and language key in table SYSTEXT2. The rows in SYSTEXT2 having those ITEM numbers and language key are retrieved in order, primarily by ITEM number and the language key, and secondarily by sequence number (SEQNO). Thus, three unique topics are returned when HELP 'VIEW' is entered.
Note that other rows in SYSTEXT1 have identical ITEM numbers but different names (TOPIC). These rows enable retrieval of each of the four topics separately. For example, the command HELP 'CREATE VIEW' retrieves only the topic having ITEM number 5260. Similarly, the 'CREATE V' entry in table SYSTEXT1 is an alias for 'CREATE VIEW'; it also points to ITEM 5260.
This cross-referencing scheme has three forms:
You can add new topics to the HELP text tables supplied by IBM, or create your own HELP text table. As modifying the HELP text supplied by IBM greatly increases the amount of administrative work required if you must later reinstall the HELP text, a much better method is to set up your own independent HELP text tables in some other PUBLIC dbspace. This method is described in Creating Your Own HELP Text Tables.
Parts of this task require DBA authority (or at least INSERT authority on the SYSTEXT1 and SYSTEXT2 tables). If you plan to add much new material to the HELP text, see Making the HELPTEXT Dbspace Larger and Moving the HELP Text to Another Dbspace.
To add your own topic to the tables, follow these steps:
SELECT * FROM SQLDBA.SYSTEXT1 WHERE TOPIC = 'candidate name'
If rows are returned, that TOPIC name already exists, and you must choose and test another.
INSERT INTO SQLDBA.SYSTEXT1 VALUES ('HOURS',1000)
INSERT INTO SQLDBA.SYSTEXT2
VALUES(1000,10,'HOURS OF USE:','S001')
INSERT INTO SQLDBA.SYSTEXT2
VALUES(1000,20,'8 AM TO 6 PM','S001')
where "S001" is the English language key. You can repeat this type of INSERT for every other language.
| Note: | The "SQL/DS HELP" column has a length of 60 characters. |
When adding HELP text to the SYSTEXT2 table, a language key must be specified. A list of valid language keys is found in Table 26. You should use installation procedures supplied by IBM.
You should consider using the SYSTEXT1 and SYSTEXT2 tables as the basis for creating your own HELP text tables. SYSLANGUAGE must still exist for the HELP command to work, unless you establish HELP text tables and query those tables as shown in Figure 31.
Figure 31 shows example SQL commands to set up your own local HELP text.
Figure 31. Implementing Your Own HELP Text Tables
CREATE TABLE SQLDBA.LTEXT1 (TOPIC CHAR(20) FOR BIT DATA NOT NULL,
ITEM SMALLINT NOT NULL)
IN "PUBLIC".LOCAL
CREATE TABLE SQLDBA.LTEXT2 (ITEM SMALLINT NOT NULL,
SEQNO SMALLINT NOT NULL,
"LOCAL HELP" CHAR(60) FOR BIT DATA NOT NULL,
LANGKEY CHAR(4) NOT NULL)
IN "PUBLIC".LOCAL
CREATE INDEX SQLDBA.LTEXT1INDEX
ON SQLDBA.LTEXT1(TOPIC,ITEM)
CREATE INDEX SQLDBA.LTEXT2INDEX
ON SQLDBA.LTEXT2(ITEM,SEQNO,LANGKEY)
...
SELECT "LOCAL HELP"
FROM SQLDBA.LTEXT1, SQLDBA.LTEXT2
WHERE TOPIC = 'topicname'
AND SQLDBA.LTEXT1.ITEM = SQLDBA.LTEXT2.ITEM
AND LANGKEY='XXXX'
|
XXXX in the LANGKEY column represents the language key.
In this example, two tables, SQLDBA.LTEXT1 and SQLDBA.LTEXT2, are created in a PUBLIC dbspace called LOCAL. Appropriate indexes are also defined. Once the tables are created, you can add topics in a way similar to that described previously for the tables of HELP text supplied by IBM. Replace the names supplied by IBM for the HELP text tables, dbspace, and column names with your own names.
Users can then access the new HELP text with an ISQL routine that contains a SELECT statement (see the example in Figure 31). The ISQL stored routines supplied by IBM for accessing the original HELP text may not work for the new tables, so it may be necessary to set up new ones. The SELECT authority must be granted to all users on the table containing the routine and on the HELP text tables.
The size of the original HELPTEXT dbspace is 8192 pages, which is sufficient to hold the HELP text supplied by IBM and four or five languages. If you plan to add extensively to the text or to add more than five languages, it may be necessary to increase the size of this dbspace.
To see how many pages are currently active in the HELPTEXT dbspace, issue the following query through ISQL or the DBS Utility:
SELECT DBSPACENAME,NACTIVE
FROM SYSTEM.SYSDBSPACES
WHERE DBSPACENAME='HELPTEXT'
If the NACTIVE (number of active data pages) value is close to 4646 (8192 minus the index pages allowance), consider making the HELPTEXT dbspace larger. To estimate how many pages are needed in the dbspace for the modified HELP text, see Appendix A, Estimating Your Dbspace Requirements.
If the estimated number of pages (for both current and future estimated usage) is greater than or close to 8192, increase the size of the dbspace. To do this, you must drop and re-create the dbspace, as follows:
Figure 32 and Figure 33 show examples of increasing the size of the "PUBLIC"."HELPTEXT" dbspace to 8448 pages. A tape is used in this example to temporarily hold the HELP information that is on your database.
Figure 32. Unloading and Reloading the HELP Text in VSE
// JOB UNLOAD HELP TEXT
// EXEC PROC=DBNAME01
// EXEC PROC=ARIS71PL
// TLBL HELPTAP,.......
// ASSGN SYS005,.......
// EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS'
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
UNLOAD DBSPACE ("PUBLIC"."HELPTEXT") OUTFILE(HELPTAP);
DROP DBSPACE "PUBLIC"."HELPTEXT";
ACQUIRE PUBLIC DBSPACE NAMED "HELPTEXT" (PAGES=8448);
RELOAD DBSPACE ("PUBLIC"."HELPTEXT") NEW INFILE(HELPTAP);
CREATE INDEX SQLDBA.SYSTEXT1INDEX ON SQLDBA.SYSTEXT1 (TOPIC,ITEM);
CREATE INDEX SQLDBA.SYSTEXT2INDEX ON SQLDBA.SYSTEXT2 (ITEM,SEQNO,LANGKEY);
GRANT SELECT ON SQLDBA.SYSTEXT1 TO PUBLIC;
GRANT SELECT ON SQLDBA.SYSTEXT2 TO PUBLIC;
COMMENT ' ** OPTIONALLY ADD SQL STATEMENTS TO GRANT AUTHORIZATIONS **
** OR CREATE ANY VIEWS REQUIRED FOR THE NEW DATA BASE. **';
CREATE VIEW ........;
·
·
·
GRANT ..............;
·
·
·
/&
Figure 33. Unloading and Reloading the HELP Text in VM
FILEDEF HELPTAP TAPn...
SQLDBSU DB(DBNAME01) IN(TERM)
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
UNLOAD DBSPACE ("PUBLIC"."HELPTEXT") OUTFILE(HELPTAP);
DROP DBSPACE "PUBLIC"."HELPTEXT";
ACQUIRE PUBLIC DBSPACE NAMED "HELPTEXT" (PAGES=8448);
RELOAD DBSPACE ("PUBLIC"."HELPTEXT") NEW INFILE(HELPTAP);
CREATE INDEX SQLDBA.SYSTEXT1INDEX ON SQLDBA.SYSTEXT1 (TOPIC,ITEM);
CREATE INDEX SQLDBA.SYSTEXT2INDEX ON SQLDBA.SYSTEXT2 (ITEM,SEQNO,LANGKEY);
GRANT SELECT ON SQLDBA.SYSTEXT1 TO PUBLIC;
GRANT SELECT ON SQLDBA.SYSTEXT2 TO PUBLIC;
COMMENT ' ** OPTIONALLY ADD SQL STATEMENTS TO GRANT AUTHORIZATIONS **
** OR CREATE ANY VIEWS REQUIRED FOR THE NEW DATA BASE. **';
CREATE VIEW ........;
·
·
·
GRANT ..............;
·
·
·
COMMIT WORK RELEASE;
The HELP text can also be moved to another dbspace if more space is needed for additional user documentation or if it needs to be moved for other administrative reasons. The current size of the HELPTEXT dbspace is 8192 pages. The dbspace to which the HELP text is being moved must be at least that size. To accomplish the move:
| Note: | The names of the tables, columns, and indexes cannot be changed. In addition, the owner name cannot change. Future reinstallations of the HELP text will assume that the original names exist in the database. |
Use the DBS Utility to produce hardcopy output of the HELP topics. Because the softcopy is stored in tables, you need only code a SELECT statement that retrieves the desired topics, and execute this statement through the DBS Utility control file input. The DBS Utility formats the output of the SELECT statement for you.
The broad categories of HELP topics and their ranges of ITEM numbers are as follows:
By using these ranges, you can code queries to retrieve various subsets of the HELP topics. For example, the following query retrieves all the SQL statements and ISQL commands (which were extracted from the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual):
SELECT * FROM SQLDBA.SYSTEXT2
WHERE ITEM BETWEEN 5000 AND 9999 OR ITEM = 30000
AND LANGKEY='S001'
ORDER BY 1, 2;
Item 30,000 (the copyright notice) must be retrieved and printed whenever you print IBM machine-readable information.
To print a copy of all messages and codes for a language, you can use a query like:
SELECT * FROM SQLDBA.SYSTEXT2
WHERE ITEM BETWEEN 10000 AND 30000
AND LANGKEY='XXXX'
ORDER BY 1, 2;
where "XXXX" represents a selected language key.
You can also enter the SQL statement described above through an ISQL terminal. When the desired HELP topics are displayed on the screen, enter a PRINT command to obtain a hardcopy. Perform the desired formatting before entering the PRINT command.
The class and number of copies desired can be specified on the PRINT command; otherwise, the defaults are used. See the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual for detailed information.
Because the HELP topics contain both upper- and lowercase characters, a print class that prints both characters should be specified. This depends on which HELP text language you select: in the case of English, for example, the HELP text contains both upper- and lowercase characters, so you should specify an appropriate print class.