Database schemas

There are various legacy methods of defining available databases, based around a text or KISAM sources file. However the recommended method is to define these within the KCML connection manager that is available with KCML 6.00 onwards. Full documentation on the Connection Manager (also known as kwebserv) is available as part of the KCML reference manual which is available from the Resources for developers section of the KCML download area on https://www.adpdsionline.com.

To define a list of ODBC databases you must first connect to the Connection Manager through your preferred web browser. As part of the KCML 6.00+ installation a listening service for the Connection Manager is created on port 790. Connect to the web address http://serverIP:790

Select 'Administration functions' and login to the server using the normal user and password. Click 'KCML System Configuration' and then 'Display/update services and their environments'. At the bottom of that page you then have the ability to define a new Connection Manager Service. For example:

Fill in the Name & Catalogue properties, choose an appropriate Database Type and then select 'Update'. The Service definition will be added to the central configuration file, kconf.xml. When you next attempt to create a client DSN you will see that the new Connection Manager Service is an available option within the KISAM Databases dropdown.

The service definition specifies the catalog table. It is this table that defines the list of available tables for that database.

The 00.GB.catlg is structured as follows:

CREATE TABLE "00.GB.catlg.dd"
(
 DESCU             VARCHAR(35)  STARTBYTE 2  NAME 'UPPER CASE Descript'  SEQUENCE 10,
 DESCKEY           VARCHAR(28)  OVERLAPS DESCU OFFSET 7  NAME 'Description sub key'  SEQUENCE 11,
 FILENAME          VARCHAR(96)  NAME 'Full filename'  SEQUENCE 20,
 SHORFILE          VARCHAR(78)  OVERLAPS FILENAME OFFSET 1  NAME 'Display filename'  SEQUENCE 20,
 DICTNAME          VARCHAR(96)  NAME 'Full dictionary name'  SEQUENCE 30,
 SHORDICT          VARCHAR(78)  OVERLAPS DICTNAME OFFSET 1  NAME 'Display dictionary'  SEQUENCE 30,
"UPDATE"           VARCHAR(1)  NAME 'Update'  SEQUENCE 40,
 LONGNAME          INTEGER(1)  DISPLAY '#'  NAME 'Long name style'  SEQUENCE 50,
"MODULE"           VARCHAR(2)  NAME 'Module'  SEQUENCE 60,
 COMPANY           VARCHAR(2)  NAME 'Company'  SEQUENCE 70,
 BRANCH            VARCHAR(4)  NAME 'Branch'  SEQUENCE 80,
 NOTUSED           VARCHAR(2)  NAME 'Not used'  SEQUENCE 90,
 PASSWORD          VARCHAR(8)  NAME 'Password'  SEQUENCE 100,
 DESCL             VARCHAR(35)  NAME 'Lower case Descript'  SEQUENCE 110
)
NAME 'ODBC catalog file'
CREATE UNIQUE INDEX "00.GB.catlg.dd_A1" ON "00.GB.catlg.dd" (DESCU)
CREATE UNIQUE INDEX "00.GB.catlg.dd_A2" ON "00.GB.catlg.dd" (COMPANY,"MODULE",DESCKEY)
CREATE INDEX "00.GB.catlg.dd_A3" ON "00.GB.catlg.dd" ("MODULE",COMPANY,BRANCH)

An optional permissions file can also be specified. This defines access permissions for the various tables of a database. It links the combination of userid, module and company to a specified priority within that combination.

The table is defined by the following SQL. Note the column names that conflict with SQL reserved words and therefore have to be enclosed in quotes.

 
CREATE TABLE "00.GB.perms.dd"
(
"KEY"            VARCHAR(12)   STARTBYTE 2  NAME <<'Key'>>  SEQUENCE 1,
"USER"           VARCHAR(8)   OVERLAPS "KEY" OFFSET 1  NAME <<'User ID'>>  SEQUENCE 1,
"MODULE"         VARCHAR(2)   OVERLAPS "KEY" OFFSET 9  NAME <<'Module'>>  SEQUENCE 2,
 COMPANY         VARCHAR(2)   OVERLAPS "KEY" OFFSET 11  NAME <<'Company'>>  SEQUENCE 1,
"PRIORITY"       VARCHAR(1)   NAME <<'Priority'>>  VALIDATE C '123456789'  SEQUENCE 4
)
NAME <<'Permissions'>>

The USER column was originally VARCHAR(5). If you want to define USER as VARCHAR(8) you must either set $LONGUSERNAMES to "TRUE" or specify the -8 flag within the odbc listening service (as defined within either kservadm, /etc/xinetd.d/kisam or /etc/inetd.conf).

To define the appropriate permissions file for that ODBC service, click the 'Add environment' hypertext link against that ODBC service where the PERMISSIONS environment variable can be set for the appropriate permissions file.