The Janus family of connectivity products provides high-speed TCP/IP connectivity to Model 204 and 5 different programmer interfaces into the database. This is a discussion of the differences between the APIs, and step-by-step implementation instructions for each interface.
As Model 204 application development shops move toward client-server architecture the existing technology and training investment can be leveraged via software that allows legacy systems to participate in the new architecture, and by planning for the complexities of new development paradigms.
Janus takes advantage of Model 204's near-relational structure to position it in the center of high performance mission-critical development. Successful implementations depend upon an up-front understanding of the new architecture including design and coding peculiarities under client-server.
Introduction
Participation in new paradigms
Under SNA-architected databases, database designers and application programmers often work very closely together to bring up a new application. Under client-server architecture the server programmers may work with the database designers, but the client - programmers will often work in a separate group, having little communication with the server developers beyond specifying the parameters and data that will pass between the two halves of the application.
Therefore, it is important to be very clear on the division between client and server application components.
There are any number of ways in which a database can be distributed and application logic be spread across a network.
How client-server is implemented at a site depends equally upon the needs of the application and the long-term role played by client-server architecture within the enterprise.
Factors to consider in determining a client-server strategy are:
|
|
|
|---|---|
| ifdial (Janus Base) | Batch2-like interface into Model 204 for use in C, C++ or other low- level languages |
| Janus Open Server | Remote procedure calls to User Language stored procedures. |
| Janus Open Client | Client calls from Model 204 outward on the TCP/IP network. |
| Janus Omni Access Module | High performance SQL access to Model 204. |
| Janus Web Server | Native Web acceess directly into Model 204. |
[This section is under construction.}
This is an overview of how to write a simple client-server connection between Visual Basic 3.0 and a Janus Model 204 Open Server port.
From a programming perspective, the simplest Janus client-server
configuration looks like this:
The components of the connection are:
Sub Form_Load ()
Dim db As Database
Dim ds As Dynaset
Set db = OpenDatabase("AHOY", False, False, "ODBC")
Set ds = db.CreateDynaset("TODAYS_GREETING", 64)
Do Until ds.EOF
List1.AddItem ds("TEXT")
ds.MoveNext
Loop
ds.Close
db.Close
End Sub
The Dim statements declare the handles to a Database and Dynaset.The OpenDatabase function call makes the connection to the port named " AHOY" via ODBC. Because no userid or password is specified in this function call, VB automatically prompts for them.
The CreateDynaset call passes the string " TODAYS_GREETING" to the database as an implied EXEC statement.
The AddItem function retrieves all rows returned by the stored procedure.
Port AHOY must have been defined to the Sybase client via the SQL Setup utility supplied with the Sybase Client software. In addition, VB's Database Setup must have been run to define AHOY as an ODBC port.
Inside Model 204, the following commands are run to define and start port AHOY:
JANUS DEFINE AHOY 5001 OPENS 5 EXEC2RPC UPCASE -
OPEN FILE PROCFILE CMD 'INCLUDE STARTUP'
JANUS START AHOY
Port AHOY (known to the TCP/IP network as port 5001 on the mainframe) is defined as an OPENSERVER port allowing 5 connections over which may be sent EXECUTE statements and implied EXECUTE statements. Uppercase translation of parameter and stored procedu- re names is automated for the port via the UPCASE setting.
Any incoming request on this port will automatically open file PROCFILE and execute procedure STARTUP.
See the Commands section of the Janus Reference Manual for a full description of the above JANUS DEFINE command.
This is what the User Language stored procedure STARTUP looks like:
BEGIN
%X IS FLOAT
%RPC IS STRING LEN 50
IMAGE GENERAL NAMESAVE
TEXT IS STRING LEN 50
END IMAGE
WAIT: %X = $SRV_WAIT
IF %X NE 1 THEN
%X = $SRV_MSG(),,'INVALID STORED PROCEDURE NAME')
%X = $SRV_DONE('ERROR')
JUMP TO EXIT
END IF
%RPC = $SRV_RPCNAME
IF %RPC EQ 'TODAYS_GREETING' THEN
PREPARE IMAGE GENERAL
%X = $SRV_SETROW('GENERAL')
%GENERAL:TEXT = 'HELLO 1996 SUG ATTENDEES'
%X = $SRV_SENDROW
%X = $SRV_DONE('')
ELSE
%X = $SRV_MSG(,,'INVALID STORED PROCEDURE NAME')
%X = $SRV_DONE('ERROR')
END IF
JUMP TO WAIT
EXIT: %X = $SRV_CLOSE('')
END
Image GENERAL is declared with the special keyword NAMESAVE, indicating it will be used to return row information to a Sybase client application.
Next, $SRV_WAIT is executed. A return code of " 1" indicates the client sent a request to execute a stored procedure. If a " 1" is not received, the connection is broken.
Next, $SRV_RPCNAME retrieves the name of the stored procedure. If the name matches "
TODAYS_GREETING"
, then "
Hello World!"
is populated into the return row image and sent. The client program will pick up this text and display it in the list1 object on the form.
Generalizing the connectivity components
Some users will find their needs are not met by a simple client-server connection.
Sites that employ both the Janus Open Server and Janus Omni Access Module may choose to allow OmniSQL Server to route both their SQL and RPC requests.
Routing RPCs through Sybase offers additional flexibility in testing and in switching between Model 204 and Sybase target databases. Users who are evaluating various front-end development tools will find that some require a catalog of stored procedures -
be kept, so certain visual structures can be written against them. For example, RPCs can only be used as a data source in PowerBuilder's DataWindow objects if the stored procedure is cataloged in SQL Server. The current release of Janus does not suppo-
rt such a catalog, but requests can be routed through procedure "
stubs"
in SQL Server, which route the execution to Janus:
Two communication links have to be established for this to work the first between the client PC and the Sybase SQL Server, and the second between SQL Server and the Janus port.
Using the same " Hello World!" sample form and server User Language as before, here's how to configure for this style of RPC.
To define the connection between the VB client and SQL Server use Sybase's port setup utility to add an entry for the SQL Server, the same as described in the previous section. This entry goes either in WIN.INI or SQL.INI, depending upon the active vers- ion of the Sybase Client code.
Once this connection is defined, use VB's Database Setup screen to go through reloading SQL Server's driver and adding the definition for the named port, linking it to the correct Sybase port number and specifying the address and network dll.
The Janus port definition has the slight added complication in that the " remote server" has to be defined. Within Model 204, execute the following commands to define our port (AHOY) and the remote SQL Server port that will pass Janus the RPC's- (OMNISQL on a server called " RS6000"
JANUS DEFINE AHOY 5001 OPENS 5 EXEC2RPC UPCASE -
OPEN FILE PROCFILE CMD 'INCLUDE STARTUP'
JANUS DEFINEREMOTE AHOY OMNISQL RS6000 2040
JANUS START AHOY
).
After these commands are executed, port AHOY in the 204 online is ready to accept RPC's either directly from a client connect or passed to it from the SQL Server at port 2040 on node RS6000.
To give SQL Server the ability to communicate with OMNISQL, there must be an entry in SQL Server's interfaces file and sp_addserver must be run for the Janus port. In addition, the logon id to be used in Model 204 must be valid in the Sybase SQL Server - as well.
You can verify the ports are available using utilities like Sybase's WDBPING.
The Visual Basic script is almost unchanged:
Sub Form_Load ()
Dim db As Database
Dim ds As Dynaset
Set db = OpenDatabase("AHOY", False, False, "ODBC")
Set ds = db.CreateDynaset("AHOY...TODAYS_GREETING", 64)
Do Until ds.EOF
List1.AddItem ds("TEXT")
ds.MoveNext
Loop
ds.Close
db.Close
End Sub
Notice the change to the CreateDynaset call. The CreateDynaset call passes the string "
AHOY...TODAYS_GREETING"
to the database. The full syntax for this statement is <
port>
.<
database>
.<
dbo>
.<
rpcname>
, but Janus -
ports disregard database name and database owner, so the middle two qualifiers are skipped, leaving a syntax of <
port>
...<
rpcname>
.When SQL Server OMNISQL receives this statement it passes off execution of stored procedure TODAYS_GREETING to port AHOY. The resulting returned data is identical to that in the first example.
Another way to have Sybase execute procedures remotely is to create a procedure in Sybase which requests the remote execution, as follows:
create procedure todays_greeting
@p1 varchar(255)
as
exec AHOY...TODAYS_GREETING
In this example, the client would request the execution of procedure todays_greeting on the Sybase SQL Server port, and the procedure holds the routing information (the "
@"
syntax shown above is the way parameters are declared and passed).This sort of setup is useful if you are going to write applications using PowerBuilder's DataWindows or if you're still trying out different front-end application packages, as a number of these packages require feedback from SQL Server about the existenc- e of the stored procedure at the time the application is being designed.
Programming Considerations / Hints
Standardizing Common Routines
The three most common system actions you'll need to do in a stored procedure application are handling error conditions and messages, and transferring control to and from "wait" points.
The "wait" procedure executes a $SRV_WAIT function. Similar to coding READ SCREENS in Model 204, this is the point at which the program receives input from the user (the client). Also, as when reading Model 204 screens, if you have only one point in a - procedure where a screen is read or a $SRV_WAIT is executed, it makes it simpler to have all procedures follow the same navigation rules and execute the same data filtering routines.
A sample "wait" procedure is shown on the next page:
*--------------------------------------------------
* Procedure: APP_R_WAIT
*
* Provides the wait point and procedure routing
*--------------------------------------------------
BEGIN
*/ Variable declarations.
%PROC IS STRING LEN 255
*/ Common routines.
IN APSPROC INCLUDE API_R_COMMON_VARIABLES
IN APSPROC INCLUDE API_R_COMMON_SUBROUTINES
*/ $SRV_WAIT must be called before an RPC is processed.
%RC = $SRV_WAIT
IF %RC NE 1 THEN
CALL ERROR( 20000, 0, '$SRV_WAIT failed.' )
CALL ERROR( 20001, 100, 'Break connection.)
END IF
*/ Retrieve the name of the RPC.
%PROC = $SRV_RPCNAME
IF %PROC EQ '' THEN
CALL ERROR(20002, 0, 'Null RPC specified.')
END IF
*/ Create a valid apsy name if it's not passed as such.
IF $SUBSTR(%PROC,1,6) NE 'APP_R_' THEN
%PROC = 'APP_R_' WITH %PROC
END IF
*/ Verify the RPC exists in APSPROC.
%X = $RDPROC('OPEN','APSPROC',%PROC)
IF $STATUS THEN
%X = $RDPROC('CLOSE',%X)
CALL ERROR(20003, 0, 'Invalid RPC name.')
END IF
%X = $RDPROC('CLOSE',%X)
%RC = $SETG('APSNEXT',%PROC)
END
Error handling routines and message handling routines are again, predictably simple. If these are standardised up front, you'll save yourself a lot of coding.Error and message routines use the $SRV_MSG function, and may look like this:
*-----------------------------------------------------
* Procedure: API_R_COMMON_SUBROUTINES
*
* Description: Routines for TCP/IP Error messages,
* AUDIT writing and general navigation.
*
* Subroutines: 1) ERROR -- Crash-and-burn errors.
* 2) AUDIT -- Write AUDIT lines.
* 3) DONE -- End procedure/SETG next.
*----------------------------------------------------
*/ Error routine: Sends a message to client, then
*/ processing goes by message class:
*/
*/ %MSG.CLS = 0: return to calling procedure.
*/ %MSG.CLS = -1: cancel out of proc and go to WAIT.
*/ %MSG.CLS = 100: severe error: break connection.
SUBROUTINE ERROR ( %MSG.NO IS STRING LEN 10, -
%MSG.CLS IS STRING LEN 2, -
%MSG.TXT IS STRING LEN 255 )
%X IS FLOAT COMMON
%X = $SRV_MSG( %MSG.NO, %MSG.CLS, %MSG.TXT )
CALL AUDIT ( %MSG.NO, %MSG.TST )
IF NOT %MSG.CLS THEN
*/ if class is 0 return to calling point.
RETURN
ELSEIF %MSG.CLS EQ 100 THEN
*/ if class is 100, break.
%X = $SRV_DONE('ERROR')
%X = $SRV_CLOSE
CALL DONE ( 'APN-APEXIT', '' )
ELSE
*/ if class is anything else, return to WAIT.
%X = $SRV_DONE('ERROR')
CALL DONE ( 'APP_R_WAIT', '' )
END IF
END SUBROUTINE ERROR
*/ Send AUDIT lines to journal. /*
SUBROUTINE AUDIT( %AUDIT_ID IS STRING LEN 12, -
%AUDIT IS STRING LEN 132 )
%ID IS STRING LEN 12
IF %AUDIT_ID EQ '' THEN
%ID = 'TCP/IP.00000: '
ELSE
%ID = 'TCP/IP.' WITH $PAD(%AUDIT_ID,'0',5) WITH ': '
END IF
AUDIT %ID WITH %AUDIT
END SUBROUTINE AUDIT
*/ End-of-processing subroutine. /*
SUBROUTINE DONE( %NEXT IS STRING LEN 255, -
%RTRN IS STRING LEN 255 )
%X IS FLOAT COMMON
%DUMMY = $SETG( 'APSNEXT', %NEXT )
%DUMMY = $SETG( 'APSRTRN', %RTRN )
STOP
END SUBROUTINE DONE
Parameter Passing
Standards for declaring and passing parameters vary with each development environment, so it's difficult to establish standards for the client application. However, Model 204 mimics Sybase via Janus, and Sybase and Janus are consistent in how parameters- are received.
%X = $SRV_PARMGET(< position> ,< parmname> ,< %variable> )
Parameters should be passed and referenced by name whenever possible rather than by position.
Parameter names should conform to naming standards for Sybase entities. Specifically excluded are the characters ~`!%^& *()+-={}[]|\:" '< > ?/, blank and the cent sign. Basically, parameter names should only contain alphanumeric characters and t- he underbar ("_"), and should begin with an alpha character.
Parameter names must be less than 31 characters long.
Reduce client / server interdependencies.
SELECT criteria
In Model 204, any field can be a FIND criterion (FIND is like SQL SELECT). If the field is not indexed, the FIND may be extremely slow, and may negatively impact all other users of the Model 204 system.
Use ORDERED INDEX fields instead of KEY. This means that any field you can search on will be available as either an exact match or a pattern match.
When passing parameters which will become FIND criteria in the Model 204 database:
If you want an exact match, pass the string to match. If you want to find all values that begin with the pattern, append an asterisk.
Null means don't find on this criterion.
An asterisk by itself means "find all".
Don't request "IS PRESENT" or "IS NOT PRESENT" searches. These are extremely inefficient. If a find must be performed based on the abscense of any value, the database should be updated to contain a specific "no value" value, or another flag field shoul- d be created.
Model 204 database design / data structure peculiarities
(Multiply Occurring Fields)
The Model 204 structure that maps onto a SQL row, is called a record. Records in Model 204 are completely unstructured: fields (columns) can exist in any order, and unless explicitly limited, may occur any number of times. Multiply occuring - fields may be seen as a performance extension of normalized design. In SQL terms, a multiply occuring field can be represented by a nested table.
Unfortunately, because there is no way to predict the number of occurrences of any field in a 204 record, Model 204 has to assume some sort of default behavior and the behavior it assumes is to pass back only the first occurrence of any field on a record- . Code has to be specifically written to process every occurrence.
This represents a special problem for RPC architected applications in general. The Janus functions return data in IMAGES, which can be constructed of any variable types, including arrays. Janus cannot, however, handle arrays with undeclared upper limit- s (these are UNKNOWN sized arrays in 204). The two choices then in sending back multiply occurring fields are 1) to code an array in the returned IMAGE which has an upper limit large enough to handle the largest number of occurrences of the field or 2) - to return multiply occurring fields separately from the rest of the record.
Take the following example Model 204 record:
DAD=JOE MOM=MARY ADDRESS=1 PINE ST., VANILLA, OHIO CHILD=ARIEL DOB=901225 CHILD=ANASTASIA DOB=920314 CHILD=ARKADY DOB=950803
Both the CHILD and DOB fields are multiply occurring fields. We could return this information in an image like this:
IMAGE RETURNDATA GLOBAL NAMESAVE ?DAD???IS STRING LEN 20 ?MON???IS STRING LEN 20 ?ADDRESS??IS STRING LEN 50 ?CHILD1??IS STRING LEN 20 ?DOB1???IS STRING LEN 6 ?CHILD2??IS STRING LEN 20 ?DOB2???IS STRING LEN 6 ?CHILD3??IS STRING LEN 20 ?DOB3???IS STRING LEN 6 END IMAGE
which assumes that the largest number of occurrences of the CHILD and DOB fields is 3. Or we could return the information in an array like this:
IMAGE RETURNDATA GLOBAL NAMESAVE ?DAD???IS STRING LEN 20 ?MON???IS STRING LEN 20 ?ADDRESS??IS STRING LEN 50 ??ARRAY CHILD_DATA OCCURS 12 ???CHILD??IS STRING LEN 20 ???DOB??IS STRING LEN 6 ??END ARRAY END IMAGE
Which accomplishes the same thing but lets us code a little more cleanly. Unfortunately, even this style of coding requires that we hardcode an upper limit to the number of occurrences (in this case 12).
In order to generalize this operation and to avoid hardcoding upper limits in the stored procedures, the following method will be used: A separate IMAGE will be coded for the "parent" section of a record and for the "child" section. The non-multiply oc- curing portion of the record will be sent first, then the multiply occuring section will be sent after. Like this:
IMAGE PARENT GLOBAL NAMESAVE
?DAD???IS STRING LEN 20
?MON???IS STRING LEN 20
?ADDRESS??IS STRING LEN 50
END IMAGE
IMAGE CHILD GLOBAL NAMESAVE
?NAME???IS STRING LEN 20
?DOB???IS STRING LEN 6
END IMAGE
...
FOR EACH RECORD IN XXX
?IDENTIFY IMAGE PARENT
?%X??? = $SRV_SETROW('PARENT')
?%PARENT:DAD? = DAD
?%PARENT:MON? = MON
?%PARENT:ADDRESS = ADDRESS
?%X??? = $SRV_SENDROW
?IDENTIFY IMAGE CHILD
?%X??? = $SRV_SETROW('CHILD')?
F1:?FOR EACH OCCURRENCE OF CHILD
???%CHILD:NAME = NAME(OCCURRENCE IN F1)
???%CHILD:DOB = DOB(OCCURRENCE IN F1)
??END FOR?
END FOR
In some cases, the "fixed", non-multiply occuring section of the record will be sent in response to one RPC, and the multiply-occuring section of the record will be sent in response to another.
Architect around SQL concepts
Most client developers think in terms of SQL verbs when referring to database activity. Componentizing your system around SQL verbs will result in an application that is easier to migrate to another database, and a Model 204 application that is more rea- dily understood by client developers.
Implementing Business Rules.
Traditional business rule implementation -- as part of database.
Business rules almost have to be implemented on both sides of the client-server application.
Avoid "Update" stored procedures
Due to the complexity of coordinating "changed" information between the client and server, it is simpler, though more resource intensive, to perform delete and insert operations: that is, delete an entire Model 204 record and re-add it at the client request, rather than trying to change only the fields specified by the client.
Maintaining Connection versus Connect/Disconnect
Support for more users than allowable threads.
Logon cost.
Using globals, global lists, global images.
Client Development
1. An ounce of planning is worth a pound of hacking.
2. Program structure with GUI development packages.
3. Modularity decreases monolithic nature and memory requirements
4. Modularity makes it diffucult to find code.
5. Plan your libraries for reuse.
Janus Open Client API
Janus Omni Access Module API
Omni SQL Access to Model 204 data
A Janus port defined as type " OMNI" acts as a generic access module to Sybase's Omni SQL Server. An Omni SQL Server accepts Transact-SQL statements and converts them into a subset of ANSI Standard SQL more easily processed by non-Transact-SQL - based servers. This SQL subset is forwarded to the appropriate server based on Omni table definitions. If the required data is in a Model 204 database the request is handled by the Janus Omni Access Module.
Because Model 204 data structures do not map naturally onto table/columns structures expected by SQL-based systems, the files must be mapped on a file/field table/column basis. So, to provide SQL access to 204, the first step is to provide a mapping, an- d this mapping is done via the JANCAT subsystem.
Once the data definitions are complete, they are made available to Omni via the 'create existing table' function (usually as automated in the 'defgen' utility).
The Model 204 to SQL mappings are normally kept in a file called JANCAT, though sites can choose to use any file(s) they want. When the Omni port is started an Omni SDAEMON (pronounced " ess-demon" ) is started.
The SDAEMON loads the table and column definitions into virtual memory and CCATEMP from the catalog file defined to the Janus port. The SDAEMON remains logged on as long as the Janus Omni port is running, holding enqueues on cataloged files if the mappi- ng user specified that a lock should be held.
The SDAEMON also reloads tables into virtual memory from the catalog file whenver the JANUS RELOAD command is executed, or when tables definitions are changed via the JANCAT subsystem for ports that are designated AUTOLOAD.
From an application programmer's point of view, Model 204 is just another SQL Server, and there is little they need to know beyond the table/column structure of the mapped Model 204 data. There is no " sp_tables" utility in Model 204, but progr- ammers can execute
SELECT * FROM JANCAT_TABLES
and then,
SELECT * FROM JANCAT_COLUMNS WHERE TABLE = xxxxxxxxxxxx
to investigate data structures in the 204 server. The tables JANCAT_TABLES and JANCAT_COLUMNS are predefined in every Janus Omni installation.
From a Model 204 System Manager perspective there is little that needs to be done with Janus Omni after the initial install. Port diagnostics can be turned on to investigate how Janus is handling the incoming SQL, but unless there is a specific performa- nce issue, there is little need for this. The commands
JANUS STATUS
JANUS STATUSREMOTE
show the current status of active Janus ports and remote server definitions.
The Janus Omni SQL implementation allows SQL access to Model 204 data without requiring normalization of the data or the undoing of peformance features of Model 204 files. Janus translates incoming SQL to User Language, and stores the User Language comp- ilations for reuse. These features create an extremely easy to use, high performance industry standard port into Model 204.
The only real work with the Janus Omni Access Module falls to the person responsible for defining the 204 to SQL table/column mappings. The JANCAT subsystem is described in detail in the Janus Reference Manual (including screen shots of the JANCAT scree- ns), so the following is just a quick overview of the process.
Mapping 204 files with JANCAT
The simplest way to define table/column mappings for 204 files is to consider each file a table, each field a column, and every record a row in the table. If a file has no " record type" structure, and no multiply occurring field this works fine- . For instance, if you have a 204 file BOOKS it could contain the fields: TITLE, PRICE, PAGES, PUBLISHER, PRINTING. This file would map nicely onto a table and set of columns because the data is homogenous -- every record describes a single book -- and- each field occurs once on each record. JANCAT would analyse this file and provide a single table called BOOKS, and a column for each field in it. Any row in the table would describe a single book.
Say however, that the database includes the file AUTHORS, and that AUTHORS has fields NAME, ADDRESS, PHONE and BOOK. Obviously, an AUTHOR may have many books, and may in fact have any number of ADDRESS and PHONEs. JANCAT can still define a mapping for - this file, but it has to introduce the concept of a " nested table" . A nested table is a field or set of fields that is physically stored on the same record, but which must be represented in SQL terms as another table linked to the main table b- y a key. For instance, say AUTHORS contains this record:
NAME=JOSEPH HELLER ADDRESS=111 5th AVE NY NY 10013 PHONE=212-555-5555 ADDRESS=12 Marmot Way East Hampton NY 11034 PHONE=518-333-3333 BOOK=Catch 22 BOOK=Something Happened BOOK=Good as Gold
In SQL terms, this record contains 2 nested tables, one for ADDRESS/PHONE and one for BOOK. It would be represented thus:
The " address/phone" table:
NAME ADDRESS PHONE
JOSEPH HELLER 111 5th AVE NY NY 10013 212-555-5555
JOSEPH HELLER 12 Marmot Way
East Hampton NY 11034 212-555-5555
and the "
book"
table:
NAME BOOK
JOSEPH HELLER Catch 22
JOSEPH HELLER Something Happened
JOSEPH HELLER Good as Gold
JANCAT would never provide an "
AUTHORS"
table, it would provide an AUTHORS_ADDRESS"
table and an "
AUTHORS_BOOK"
table, and to rebuild the original 204 record in a SQL client, the client would have to execute the following stateme-
nts:
SELECT * FROM AUTHORS_ADDRESS ?? WHERE NAME=JOSEPH HELLER SELECT * FROM AUTHORS_BOOKS ?? WHERE NAME=JOSEPH HELLERThere's one more level of complication in mapping 204 files onto SQL tables. In 204, the above two files could be contained in a single file called, say, PUBS.
In PUBS we would have a RECTYPE field to distinguish those records that contained details on BOOKS from those that contained details on AUTHORS.
JANCAT would map pubs as 3 tables, calling them
PUBS_BOOKS
PUBS_AUTHOR_BOOKS
PUBS_AUTHOR_ADDRESS
The first qualifier in JANCAT's table naming convention, is the file name. The second qualifier is the value (if any) of the RECTYPE field it is based on. The third qualifier is the variable that creates the nested table.
Before entering JANCAT to map files, be ready with a list of the names of the files you want to map, and the record type field (if any) each file. If you have a table-naming standard you want to implement that's different from JANCAT's default naming as- described above, be ready with the table names you'd like to use.
The first time the user enters the JANCAT subsystem, there should be 4 tables visible, JANCAT_TABLES, JANCAT_COLUMNS, JANCAT_GROUPS, and JANCAT_SECURE. These are the " meta-tables" that describe JANCAT itself, and allow users to retrieve table - information using SQL.
To add a new table, place the cursor on the top line of the screen (the command line beginning with " ==> " ) and type
NEW < table name> or
ADD < table name>
There will be a delay while JANCAT takes a sample set of records from the designated file and tries to determine the structure.
The next screen allows the user to specify certain characteristics of the SQL mapping. JANCAT's guess as to the record type field is presented -- sometimes multiple " guesses" are presented. If there is no record type structure in the file, th- e record type field should be blanked out.
JANCAT also allows the user to change the default table name prefix (the default is always the file name).
In addition, the user can choose several other options. Read the help text (available via PF key 1) to determine which options to take.
Press the < enter> key to initiate the mapping. On a very busy system, for a complicated file, the mapping may take as long as 10 minutes or more.
When control returns to the user, the file will be completely mapped.
If a Janus Omni port is started with the AUTOLOAD facility, the new table data will be immediately available for Omni access. Otherwise, the JANUS RELOAD command or JANUS START command must be executed to reload the table definitions to the port or to s- tart the port with the new table definitions.
Repeat the process for each file to be mapped.
Run the Omni utility 'defgen' to make Omni aware of the table names available on the Janus port.
There are a few points to keep in mind when mapping and using Model 204 data via Omni SQL:
Janus does not process SQL. Instead it translates incoming SQL into Model 204 User Language, stores the compilation in virtual memory, and executes it. Stored UL compilations allow Janus to execute incoming SQL requests extremely efficiently, however, - under the covers a Table B scan is still a Table B scan, and a large request is still a large request, therefore:
1. Never use a non-KEY / non-ORDERED field as the record type field. The record type field becomes part of the FIND for any incoming SELECT statement, and generates the same inefficiencies as it would in " old" 204 if searches are done in a non- -indexed manner.
2. The same goes for columns (fields) used in WHERE clauses.
[This section is under construction.}
