KB_SQL Database Administrator s Guide Version 4

(1)

KB_SQL

Database

Administrator’s

Guide

(2)

Printed in the United States of America.

No part of this manual may be reproduced in any form or by any means (including electronic storage and retrieval or translation into a foreign language) without prior agreement and written consent from KB Systems, Inc., as governed by United States and international copyright laws.

The information contained in this document is subject to change without notice. KB Systems, Inc., does not warrant that this document is free of errors. If you find any problems in the documentation, please report them to us in writing.

KB Systems, Inc.

585 Grove Street, Suite 201 Herndon, Virginia 20170-4705

Voice (703) 318-0405 FAX (703) 318-0569 E-mail [email protected]

WWW http://www.kbsystems.com

KB_SQL is a registered trademarks of KB Systems, Inc.

MUMPS is a registered trademark of the Massachusetts General Hospital. All other trademarks or registered trademarks are properties of their respective companies.

(3)

Preface

Purpose

The KB_SQL Database Administrator’s Guide contains information about maintenance procedures for KB_SQL. This document is designed to help the database administrator (DBA) use the KB_SQL procedures effectively to ensure good performance and consistent operation of the system.

Audience

The manual is written for the database administrator of the KB_SQL system. The material is directed to those persons responsible for the smooth ongoing operation of the system. Experienced users and programmers will find this information helpful in the basic implementation of the system and for integrating the system with existing applications.

We expect you to be familiar with M, the relational database model, and SQL. For those who want to increase their understanding of these topics, we have provided a list of publications in the “Additional Documentation” section of this preface.

We also suggest that you review Lesson 1: The Basics in the KB_SQL

SQL Reference Guide to become familiar with the functions of the

(8)

Conventions Used in this Manual

To help you locate and identify material easily, KB Systems uses the following style conventions throughout this manual.

[key]

Key names appear enclosed in square brackets.

Example: To save the information you entered, type Y and press [enter].

{compile-time variables}

References to compile-time replacement variables are enclosed in curly braces. The names are case sensitive.

Example: {BASE}

italics

Italics are used to reference prompt names (entry fields) and terms that may be new to you. All notes are placed in italics.

Example: The primary key of the table is defined as the set of columns that is required to retrieve a single row from the table.

Windows

The manual includes many illustrations of windows. Window names are highlighted by a double underline.

(9)

Prompt: data type (length) [key] The manual includes information about all of the system prompts. Each prompt will include the data type, length, and any special keys allowed. If the prompt is followed by a colon (Prompt:), you may enter a value for the prompt.

If a prompt is followed by an equal sign (Prompt= ), it is for display purposes only.

If the prompt is followed by a question mark (Prompt?), you can enter a value of YES or NO.

^GLOBAL

All M global names will be prefixed by the '^' character.

Tag^Routine

All M routine references will appear as tag^routine.

Menu Option/Menu Option/Menu Option

A string of options shows you the sequence in which you must select the options in order to arrive at a certain function. Each menu’s option is separated by a slash (/).

Example:

(10)

The Organization of this Manual

Chapter 1: An Overview of the KB_SQL User Groups and Menus

describes each user group and the menu options to which they have access. KB_SQL is delivered with three user groups.

Chapter 2: Essential Globals and Routines describes the globals and

routines that are essential to the operation of KB_SQL. It also lists the demonstration globals used for the KB_SQL tutorials.

Chapter 3: Configuring KB_SQL for Your Site walks you through the

procedures that KB_SQL provides for site configuration.

Chapter 4: Managing Devices describes the process of defining your

site’s logical and physical devices. Instructions for developing your own input translation routines and guidelines for using different types of printers and printing arrangements are also provided.

Chapter 5: Applying Security Measures discusses the security levels

that you can apply.

Chapter 6: Utilities describes several utility procedures for maintaining

the system.

Chapter 7: System Status explains the system status window and

options.

Chapter 8: KB SQL’s Version Information shows you how to view

site-specific information.

Chapter 9: Transferring Data Dictionary Objects offers guidelines and

examples for interfacing KB_SQL to your existing set of application software. It includes a description of the transfer utility that can be used to distribute information between multiple KB_SQL systems.

(11)

Chapter 10: External Interfaces shows how to compile and run queries

outside of the standard KB_SQL interface.

Chapter 11: Miscellaneous Interfaces provides information on some of

the internal functions used by KB_SQL.

Appendix A: Integrity Check explains the Integrity Check procedure

which you can use to evaluate the integrity of the KB_SQL tables.

Appendix B: The Handling of Null Values describes how KB_SQL

handles null values.

**********

Error Messages

Earlier versions of this manual listed error messages in a separate appendix. Error messages are now available by running the SQL_ERROR_LIST query.

(12)

Additional Documentation

This DBA guide is the primary reference document for the database administrator. The following publications are recommended to all users of KB_SQL.

Database: A Primer, C.J. Date, Addison-Wesley Publishing

Company, 1983.

An Introduction to Database Systems, C.J. Date, Addison-Wesley

Publishing Company, Volume I - 3rd edition, 1981, and Volume II - 1st edition, 1983.

A Visual Introduction to SQL, J. Harvey Trimble, Jr. and David

Chappell, John Wiley & Sons, Inc., 1989.

Lan Times Guide to SQL, James R. Groff and Paul N. Weinberg,

Osborne McGraw-Hill, 1994.

Additional materials are available from KB Systems, Inc. which provide a user’s perspective of the system.

KB_SQL EZQ Reference Guide KB_SQL SQL Reference Guide KB_SQL Syntax Guide

KB_SQL SQL Pocket Guide

These documents provide self-paced tutorials as well as detailed reference information for your users.

(13)

KB_SQL is shipped with three default user groups: DBAS,

SYS_MGRS, and USERS. This chapter discusses the menu options, passwords, and responsibilities of each group. To create your own user groups and users, refer to Chapter 5: Applying Security Measures.

At the end of this chapter, we discuss issues that you need to address to help your users obtain the most benefit from the SQL Editor and EZQ Editor tutorials.

The user has a specialized understanding of a particular information area within the

Information System. USERS

The system manager has a detailed understanding of the daily operations of the Information

System. SYS_MGRS

The database administrator has a comprehensive understanding of the Information System, the data dictionary, and the user requirements. DBAS

Description User Group

An Overview of the KB_SQL

User Groups and Menus

(14)

The Database Administrator (DBA)

The database administrator (DBA) and system manager (SysMgr) are responsible for the KB_SQL system maintenance. The DBA may be a single person or a team of people, each with different skills and

expertise. The DBA is responsible for mapping existing M globals into the data dictionary, defining terminals and printers, and establishing security to ensure that users have access to the information that they need. In addition to these basic functions, the DBA is responsible for customizing the system to meet the special needs of the users.

The DBA usually has a good understanding of the client’s business. This understanding includes knowing the objectives of the client software applications and knowing the internal database design that supports the client applications.

Because of this level of expertise, the DBA is granted the DBA

privileges for the system. All individuals with the DBA privileges have unrestricted access to all functions provided by the system. They can access all tables and queries in the system.

We recommend that every installation identify a DBA to serve as the liaison between KB Systems, Inc. and the system users. The DBA is the primary source of information about the users’ needs and

expectations. By communicating directly with the technical support staff of KB Systems, the DBA can provide users with the best services.

(15)

SHARK: The DBA Password

To sign onto the system with full DBA privileges use the SHARK password. Use SHARK to sign on, then create a secret password for yourself and delete the SHARK password.

The DBA Menu

Select DBA OPTIONS

The SQL EDITOR option gives you access to the SQL Editor. Refer to the KB_SQL SQL Reference Guide and the KB_SQL Syntax Guide.

The EZQ EDITOR option gives you access to the EZQ Editor. Refer to the KB_SQL EZQ Reference Guide.

(16)

CONFIGURATION has its own menu of options. These options let you customize the implementation of KB_SQL.

Select CONFIGURATION

BASE ROUTINE EDIT allows you to specify the naming conventions for generated query routines.

EXPORT METHOD EDIT allows you to customize standard methods for downloading data from SQL tables.

FUNCTION EDIT allows you to create user functions that can be used within KB_SQL.

IMPORT METHOD EDIT allows you to customize standard methods for uploading data into SQL tables.

PSEUDO COLUMN EDIT allows you to give names to system constants that might be used in queries.

SITE EDIT allows you to maintain a profile of a particular installation of KB_SQL.

(17)

REPORTS corresponds to the reports that you may generate for each of the CONFIGURATION options.

DATA DICTIONARY Option

DATA DICTIONARY has its own menu of options. These options, which are discussed in the KB_SQL Data Dictionary Guide, allow you to create a relational view of an existing set of M application globals.

Select DATA DICTIONARY

The MAP EXISTING GLOBALS procedure defines tables, primary keys, foreign keys, and indexes.

The DOMAIN EDIT procedure allows you to describe various data storage strategies to KB_SQL.

KEY FORMAT EDIT is used to describe primary key formats when key values are stored differently from non-key values (e.g., a

descending date).

OUTPUT FORMAT EDIT is used to describe various display formats.

(18)

REPORTS corresponds to the reports that you may generate for each of the DATA DICTIONARY options.

TERMINALS/PRINTERS Option

TERMINALS / PRINTERS has its own menu of options. These options allow you to control the many different types of input and output devices.

Select TERMINALS/PRINTERS

DEVICE TYPE EDIT allows you to define device characteristics.

LOGICAL DEVICE EDIT allows you to assign a logical device.

REPORTS corresponds to the reports that you may generate for each of the TERMINALS / PRINTERS options.

(19)

SECURITY Option

SECURITY has its own menu of options. These options allow you to control access to KB_SQL procedures, tables, and queries.

Select SECURITY

GROUP EDIT allows you to assign collective privileges to users who have similar information requirements.

USER EDIT allows you to define an individual user of KB_SQL.

PUBLIC PRIVILEGES allows you to maintain privileges on tables and queries that are available to all users.

REPORTS corresponds to the reports that you may generate for each of the SECURITY options.

(20)

UTILITIES Option

UTILITIES has its own menu of options. These options include various procedures that you might use in the regular maintenance of KB_SQL.

Select UTILITIES

COMPILE ALL QUERIES is useful when you need to compile a large number of queries.

EXPORT and IMPORT allow the transfer of various objects such as tables, queries, functions, pseudo columns, and device types between different KB_SQL systems.

HALT QUERY stops queries running in the background.

JOB WATCH shows who is using KB_SQL and what they are doing.

LOCK STATUS lets you set or reset system-wide locks. This is in place of using the SYSLOCK^SQL routine.

(21)

STATISTICS creates volume and distribution statistics for the tables in the data dictionary.

REPORTS and TRANSACTION LOGS correspond to the reports and logs that you may generate for each of the UTILITIES options.

SYSTEM STATUS Option

SYSTEM STATUS displays a summary of the current status of your system.

(22)

VERSION INFORMATION Option

VERSION INFORMATION displays information about the customer and the KB_SQL product.

(23)

The DBA’s Responsibilities to the End User

The DBA plays a very important role in the introduction of the EZQ and SQL Editors in a user environment. The DBA must manage the expectations of the users against the capabilities of the application. A clear understanding of these expectations will help avoid frustrating the users and overwhelming the DBA with support requests— the reasons for implementing the report writer in the first place.

First, it is important to expect that the end users will need some training on the database of their organization. Translation of what they know about the data into the relational database world can take some time. The users will become more capable as they understand more about schemas, tables, columns and other database concepts. It is important to start out slowly, with simple data requests.

The DBA should meet with the users to determine their information processing needs and to evaluate their experience levels. This

preparation will produce simple, useful views that the users can work with to satisfy their basic information analysis requirements. By using views, the DBA can shield the user from some difficult concepts such as joins, foreign keys, and subqueries. A view looks like a simple table that includes all the data that they need.

(24)

Tutorial Issues

The tutorials in the KB_SQL EZQ Reference Guide and the KB_SQL

SQL Reference Guide can be valuable tools in the user certification

process. We recommend that all users work through the appropriate tutorial. Using simple tables and report requests, the tutorials illustrate the fundamentals of each editor. For optimal performance, the

following checklist should be reviewed before requesting users to complete the tutorials.

1. Check security issues.

{ Assign user codes as needed.

{ Ensure that the users have select access to the Employees, Projects, and Tasks tables.

{ Ensure that no extra columns are available in any of the tables. { Suggest a naming convention for the tutorial examples (e.g., prefix

query names with their initials).

2. Check the following site edit parameters.

{ Set SHOW_COST, SHOW_PLAN, SHOW_STATS and AUTO_SELECT to NO

{ Set Include NULL values in order by/group = YES 3. Make it interesting.

{ Set up a follow-up session using data that is more meaningful to the user group.

4. Allow sufficient time.

{ Some people will need 3-4 hours to complete a tutorial.

{ Schedule time to use a quiet work area, away from telephones and other distractions.

(25)

The User Profile Checklist

Above all, it is important for the DBA to know the characteristics of the users that will be using the EZQ and SQL Editors. The following checklist will help you begin to organize your users and determine their training needs.

What types of users do you have? { decision makers { department heads { directors

{ analysts { clerks

How would you expect those users to interact with the system? { fully formatted reports

{ tables of data for analysis { combination

How often would the system be accessed? { several times a day

{ several times a week { several times a month

How much training will be necessary? { KB_SQL training

{ EZQ training { Database training

Will the M data be used by other application products? { Spread sheets: Lotus 1-2-3, EXCEL

(26)

The System Manager (SysMgr)

The system manager, or SysMgr, plays a very important role. Unlike the DBA who has a comprehensive understanding of the corporate computing environment, the system manager may have very specialized knowledge. The system manager is often the person or persons who manage the daily operation of KB_SQL as one of many applications running at a customer site.

The SysMgr is presented with a limited view of the KB_SQL system. Like the DBA, the SysMgr has privileges on all tables, views, and queries. Unlike the DBA, the SysMgr cannot modify table definitions or M code. You will notice that the SysMgr menu does not include menu options for several procedures that are to be used by the DBA only.

KB_SQL comes with a SYS_MGRS user group complete with a SysMgr user. Users in this group can access all tables, views, and queries but not all menu options. In addition, the SysMgr can be restricted from parts of procedures that allow the entry or modification of M code.

Note: Any user can be a “programmer.” Only programmers can access certain input fields in which they can enter/modify M code. To designate a user as a programmer, use the SECURITY/USER EDIT procedure.

MGR: The SysMgr Password

To sign onto the system with system manager privileges use the MGR password. Use MGR to sign on, then create a secret password to be used by the system manager and delete the MGR password.

(27)

The System Manager Menu

Even though the Select SYSTEM MANAGER OPTIONS menu displays the same menu options as the Select DBA OPTIONS menu, some of the menu options limit the system manager to only certain procedures.

Select SYSTEM MANAGER OPTIONS

The SQL EDITOR option gives you access to the SQL Editor. Refer to the KB_SQL SQL Reference Guide and the KB_SQL Syntax Guide.

The EZQ EDITOR option gives you access to the EZQ Editor. Refer to the KB_SQL EZQ Reference Guide.

(28)

CONFIGURATION Option

The system manager is limited to the SITE EDIT procedure.

DATA DICTIONARY Option

The system manager can not change the data dictionary and has access only to the TABLE PRINT and VIEW PRINT report options. If the system manager selects VIEW PRINT, the Print View List window appears in which the system manager may enter a range of views to be printed. If the system manager selects TABLE PRINT, the Print Tables window appears.

Schema name: character (30)

The Schema name is optional. If entered, the table range is limited to those tables within this schema.

(29)

From table name: character (30) The From table name designates the first table (alphabetically) that you want to include in this report.

Thru table name: character (30)

The Thru table name designates the last table (alphabetically) that you want to include in this report.

Print globals? YES/NO

If you want to print the M data structures from which the tables obtain their data, answer YES; otherwise, answer NO.

Break at table? YES/NO

If you want the report to start a new page when it encounters a new table, answer YES; otherwise, answer NO.

TERMINALS / PRINTERS Option

System managers can not define or modify device types. They do have access to the LOGICAL DEVICE EDIT procedure and can print reports of the device type definitions and the defined logical devices.

(30)

SECURITY Option

The SECURITY option provides the system manager with the same capabilities as the DBA’s SECURITY option.

UTILITIES Option

The UTILITIES option provides the system manager with most of the same capabilities as the DBA’s UTILITIES option. The system manager is not able to assign fuzzy sizes or fuzzy densities to tables.

SYSTEM STATUS Option

This option displays the same information as does the DBA’s SYSTEM STATUS option.

VERSION INFORMATION Option

This option displays the same information as does the DBA’s VERSION INFORMATION option.

(31)

The User

Unlike the DBA who is responsible for the comprehensive

understanding of the environment and the SysMgr who is responsible for ensuring smooth daily operation, the user is concerned only with retrieving information from the database. Users may use the EZQ Editor to write reports on those tables that they have privileges to see. Some users may also use the SQL Editor for complete SQL capabilities and for additional customization of their reports.

KB_SQL comes with a USERS user group complete with a user defined as USER, with a password of USER. The USERS user group comes defined with the privileges to access only the demonstration tables, views, and queries. You can also assign KB_SQL users an EZQ or RUN password depending on the privileges you want to extend to them.

Password for users who only run queries; these users do not create or edit queries RUN

Password for users of the EZQ Editor only

EZQ

Password for all SQL and EZQ users other than DBAs and system managers USER

Description Password

(32)

The USER Password Menu

This user may select either the EZQ EDITOR or the SQL EDITOR option. This user can then insert, edit, delete, or run queries.

The EZQ Password Menu

This user is not given a selection window; instead the user is shown the Select Query window in the EZQ Editor. This user can insert, edit, delete, and run queries in EZQ.

The RUN Password Menu

This user can choose to run any of the queries to which he has access, regardless of how the query was developed (i.e., through the EZQ Editor or through the SQL Editor).

Note: A query will not be available to the user unless the developer of the query compiles it.

(33)

KB_SQL is delivered in M (MUMPS) and is designed to run on your existing hardware and software platforms. Your license is specific to the M type requested when you ordered the software — the product cannot be moved using routine and global copy methods.

All programs and globals delivered with KB_SQL start with the SQL prefix. This chapter lists the globals and routines essential to the operation of KB_SQL.

This chapter is designed to act as a map so that technical support personnel can quickly identify routines and globals by their function.

Essential Globals and Routines

(34)

The Essential Globals

The following globals are essential to the operation of KB_SQL. The ^SQL global will always exist. The temporary globals will be created and deleted as needed by the system.

If you are running multiple copies of KB_SQL on a network, you can keep the ^SQL global on the server, with all other temporary globals on the client systems. Consult your system administration guide provided by your M vendor for more detailed information.

P

- Persistent globals

Default location for user tables P

^SQLT

Temporary global for M code build data ^SQLS

Temporary data for queries processed through API

^SQLRD

Global used for locking only ^SQLQ

Temporary global used for parsing, planning, and building

^SQLP

Globals used for locking only ^SQLLand ^SQLCK

Temporary global for transaction related data ^SQLJ

Temporary global used by the import utility ^SQLIX

Temporary global used by text editor ^SQLIN

Info from last Integrity Check P

^SQLIC

Temporary global used by the export utility ^SQLEX

Temporary data for cursor-saved ESQL queries

^SQLCUR

The KB_SQL data dictionary P

^SQL

Description Global Name

(35)

The Essential Routines

External interface to Client API SQLOC

External interface to Server API SQLOS

SQL default base routine prefix SQLX* SQL windows SQLW* SQL internal subroutines SQLK*, SQLV* SQL procedures SQLU* SQL reports SQLR*

SQL data dictionary integrity check routines SQLI*

SQL compiler SQLG*

SQL data dictionary filers SQLF*

SQL standard device drivers SQLD*

Reserved for customer use SQLA*,SQLB*

Implementation-specific routines SQL0*

The sign on routine SQL

Description Routine

Any modifications that you make to these routines (except the SQLA and SQLB routines) can be overwritten by an update of the system. Therefore, if you must change one of these routines, use a

client-specific naming convention so that your changes will not be lost.

(36)

The Demonstration Globals

A set of demonstration data is included with the system. The

SQL_TEST schema includes the EMPLOYEES, PROJECTS, TASKS, and CHARGES tables. The data for these tables is included in the globals listed below.

The data for these globals can be restored from the DEMO.DAT file. The data dictionary information is found in the DEMO.DB file. A set of demonstration queries is in the DEMO.Q file.

Project and Task data ^SQLPROJ

Employee name index ^SQLEMPN Employee data ^SQLEMP Charge data ^SQLCHG Description Global

This information is referenced by the KB_SQL tutorials, examples, and illustrations. The content is intentionally simple so as to promote easy understanding. Your database will be more complex. The

demonstration tables, views, and queries can provide you with the fundamentals that you can then apply to your own situation.

(37)

We realize that each site is unique with different needs and

requirements; therefore, we have designed KB_SQL so that you may customize it for your site. We provide you with the tools to add new features and modify the system to best satisfy your site’s requirements and your users’ needs.

Some modifications are as simple as changing a value from NO to YES. Other changes require some M code or perhaps an M routine. All of the changes are managed by the options available in the

CONFIGURATION menu. When you make a change or add a new feature, the changes that you make are indistinguishable to the user from the built-in features provided with KB_SQL. Although it does not happen often, our changes to variable or program names may cause

Configuring KB_SQL for Your Site

(38)

Note:

All M code executes and device type executes in this chapter are for illustrative purposes only.

(39)

BASE ROUTINE EDIT Option

KB_SQL uses base routines to assign names to the M routines that it generates to perform your queries. Typical base routine prefixes will be 1-4 alpha characters in length and allow between 9 and 999 base routines. The combination of prefix and maximum number can not exceed seven characters. Creating multiple base routines will allow different user groups to produce queries with different routine prefixes.

Note: To control which routine suffixes are allocated, refer to the CONFIGURATION/SITE EDIT/CUSTOM LOGIC/GENERATE ROUTINE option.

After you select the BASE ROUTINE EDIT option, the Select, Insert, Delete selection window will appear. You can press [insert] to add a base routine, or select the base routine you wish to edit. If you want to delete a base routine, highlight it and press [delete].

Add

Adding a new base routine is as easy as deciding on a prefix and how many different routines to allow. You can use the generic SQLX prefix or a prefix specific to a site or group. Some research should be done to ensure that there are no routines in conflict with the new prefix. It is usually best to keep base routines distinct and uniform in length. For

(40)

Edit

Editing the base routine prefix is not recommended. If M routines have been allocated for queries, you should make sure that no conflicts will result from the change. We also recommend that you do not decrement the maximum number of routines for a base routine.

Delete

You can delete a base routine as long as no user group or site is linked to it. You should be sure to clean up any M routines that may be defined with the obsolete prefix.

Base Routine Information

Routine prefix: character (4)

The routine base can be 2-4 characters. Once defined, this base routine will be used to generate unique M routine names for queries.

Maximum counter: integer (3)

You can specify a maximum number of routines for this base. If the maximum is reached, and all routine names are in use, the system will not generate any more routines with this prefix.

Note: The combined sizes of the routine prefix and the maximum counter should not be greater than seven characters. This allows for one extra character that KB_SQL may need to uniquely identify the routine for the query.

(41)

Current: integer (3)

The current number of queries with this prefix. This is a display field only.

Description: character (60)

Provide a description for this base routine.

Sample Configuration

A typical configuration will have a base routine allocated for use by the entire site, with additional base routines for certain user groups. In the sample configuration below, the site has dedicated all routines starting with the characters AA for KB_SQL queries. User groups A and B each have their own prefixes, BA and BB respectively. User group C creates queries using the site prefix. The configuration allows for 999 different queries in the AA prefix, 99 different queries in the

BA prefix and 99 queries in the BB prefix.

Group_A Group_B Group_C Site Base_AA (999) Base_BB (99) Base_BA (99)

(42)

Routine Name Assignment

As queries are deleted, the query routine name is placed on a list of free routines. When a new query is created, the query will use a routine name from the free list before attempting to create a new one. If the maximum number of query routine names are currently in use, a message will be displayed. If this happens, you may decide to increase the maximum number of routines for the affected prefix.

A routine name is assigned to each query the first time the query is compiled. Deleting these queries will add the unused routines to a list of free routines.

The actual query name is composed of the base routine prefix and a numeric counter. The diagram on the next page illustrates how routines are allocated for the following sequence of events:

1. A user in GROUP_C creates QUERY_1 that requires 1 routine.

2. A user in GROUP_C creates QUERY_2 that requires 2 routines.

3. A user in GROUP_A creates QUERY_3 that requires 1 routine.

(43)

M routine name for query BB1B 1 BB 3 BB1A 1 BB 2 BB1 1 BB 1 QUERY_4 BA1 1 BA 1 QUERY_3 AA2A 2 AA 2 AA2 2 AA 1 QUERY_2 AA1 1 AA 1 QUERY_1 M ROUTINE(S) COUNTER BASE PGM QUERY

(44)

EXPORT METHOD EDIT Option

KB_SQL provides you with a set of standard formats for transferring data. These standard formats may be all that you ever need in your environment. (Refer to the Format and Method entries in the KB_SQL

Syntax Guide for more information on using these standard formats.)

If you need a custom format, the EXPORT METHOD option lets you create your own. To designate a specific export method in a query, include the METHOD parameter before the SELECT statement. (You may also specify a custom method as your site’s default. Refer to the SITE EDIT option.)

Note: As an alternative to exporting data to be used in another

application, you may want to consider using KB_SQL’s ODBC feature which lets you use Windows applications directly to access your M data. The KB_SQL ODBC Driver Install Guide gives you instructions for making this possible.

If you have not defined any export methods, the Add window will appear when you select the EXPORT METHOD EDIT option. Select YES to add an export method. Otherwise, if export methods do exist, the Select, Insert, Delete selection window will appear. You can press [insert] to add an export method, or select the export method you wish to edit. If you want to delete an export method, highlight it and press

(45)

The example that follows shows you how to use the capture mode of a terminal emulator to create a data file on your PC with data from your host system.

Export Method

Name: character (30) [list]

The export method name can be referenced in a SQL statement that directs output to a file using a custom format.

Provide a description for the export method.

Prompt for file? YES/NO

By default, the system will prompt for a filename to receive the data output from the query. Answer NO if you do not require a filename for your export method.

Show rows inserted? YES/NO

By default, the system will display a message indicating the number of rows output by the query. Answer NO if you wish to suppress this message.

Must run in foreground? YES/NO

If this prompt is set to YES, any query that uses the method will not be allowed to run in the background.

(46)

In this example, after you supply information in the Export Method window, KB_SQL prompts you to enter logic to turn on capture mode, execute the transfer, and terminate capture mode.

Note: The code in the following windows is sample code as it applies to our PROCOMM example.

Pre Process Execute

This example executes a page feed, turns off echo, hangs for one second, then transmits an escape sequence. The terminal emulator recognizes the escape sequence and responds by turning on capture mode. (This code is for illustrative purposes only.)

Row Execute

As the system formats each row for output, this execute simply writes the row variable (ROW) followed by a linefeed. (This code is for illustrative purposes only.)

(47)

End of File Check Execute

This prompt can contain an M execute that checks if the end-of-file condition has been met. If this condition is met, the prompt sets SQLERR="End of File Reached". The end-of-file condition means the export has exceeded some finite limit (i.e. the space available on a floppy disk), and represents an error condition. In the example below, the export contains more than 99, 999 rows.

Post Process Execute

When all rows have been transferred, this execute terminates capture mode, hangs for one second, restores echo mode, writes a page feed, and sets the refresh window variable (SQLWIN). (This code is for illustrative purposes only.)

(48)

FUNCTION EDIT Option

Functions are used to extend the SQL syntax by allowing an M routine to be invoked from within a query expression. A function can be used as a column reference in any SQL statement. Functions can be very valuable for using your existing M routines that perform application-specific operations.

For example, you may have an M subroutine in a financial application that calculates a price for a given item. Instead of duplicating the complex logic each time you need the calculation to be performed, you can define a function that calls the subroutine. Any query that wants to use the logic can then reference this function.

When you select the FUNCTION EDIT option, the Function Name window appears. You may press [list] to display existing function names or enter the name of the function. If you enter a new function name, the Add window appears. Select YES to add the function. Otherwise, if you pressed [list] from the Function Name window, a selection window appears. You can press [insert] to add a function, or select the function you wish to edit. If you want to delete a function, highlight it and press [delete].

(49)

Function Name

Add

You need to follow some simple steps to define and use a function. First, once a need is established, determine the number and type of input variables. Then determine the type and format of the return variable. After you specify these parameters, using this procedure, any query can reference the function.

Edit

Be sure to determine the potential effect of any change to a function definition. If the function has been widely used, you may decide that a new function is required. If not yet implemented or if the definition is incorrect, simply change the definition and compile the affected queries.

Delete

As in the case of editing a function definition, you must evaluate the potential effect of deleting the function. Any queries that use the function will require modification.

Note: The following examples are for a user-defined function that gets the first transaction record for a given date.

(50)

Function Information

Name: character (30)

Each function must have a name to identify it within the SQL syntax. The name must be a valid SQL_IDENTIFIER.

Note: An SQL_IDENTIFIER is a name, starting with a letter (A-Z), followed by letters, numbers (0-9), or underscores '_'. The last character in the name cannot be an underscore. The length of the name must not exceed 30 characters.

Description: character (60) Provide a description for the function.

Syntax: character (65)

Provide the syntax for the function.

Parse check routine: character (20) This routine checks if the correct number of arguments have been included in the function reference. If possible, use the standard entry points in the SQL0FC routine. See Chapter 11 for details.

(51)

Result domain: character (30) [list] The result domain determines how the result value is to be formatted for display. For example, our TX_FIRST function returns a value that qualifies as an integer value, with a maximum length of ten characters. Many functions will return a result in either INTEGER or

CHARACTER format.

Length: integer (3)

Enter the maximum length for the result value. Note that this prompt is asked only for domains with variable length. It will not be asked for the DATE, TIME, or FLAG domains.

Scale: integer (1)

For numeric data types, you must also enter the number of integers to the right of the decimal point.

Inline function: YES/NO

Any user-defined function can be specified as in-line. By default, a function is implemented as a call to an external M routine. If in-line is indicated, the code for the function will be placed in the generated query routine. In many cases, this can have a positive effect on performance.

Example #1, #2, #3: character (60) You can provide up to three examples of how to use the function.

If you indicate that this function be specified as in-line, the

Compile-time M Code window will appear for you to enter the code for this function. In the example below, note the use of the curly braces around the VALUE terms. When specifying an in-line function, these braces specify variables that will be replaced with their contents at query build time. For functions that are not in-line, the VALUE terms are still used, but without curly braces. Their contents are evaluated at runtime.

(52)

Compile-time M Code

Runtime Routine

Runtime routine: character (20)

The runtime routine is used to calculate the result of the function using the value array. Use routine names that do not conflict with the

KB_SQL routine names or your generated query routines. This prompt will display if you enter NO at the Inline function prompt.

(53)

The purpose of the runtime routine is to return the desired value based on the values of the specified arguments. The query will set up each argument value in a subscripted variable VALUE, with the variable VALUE equal to the number of arguments. Your runtime routine should also return the result value in the variable VALUE.

For example, a function which allows two arguments might call your runtime routine with the following data:

P VALUE = 2

P VALUE(1) = 1st argument P VALUE(2) = 2nd argument

Your runtime routine will perform any necessary calculations, then return the function result in the same VALUE variable.

For example, consider the following simple function for age calculation.

P Name= AGE

P Parse Routine= A2^SQL0FC P Run Routine= AGE^SQLAFUN P Result Domain= integer (3)

The run routine AGE^SQLAFUN might look like this:

SQLAFUN ; Site functions ;

(54)

How to Use a Function

To create a new function you must provide the function name, an optional parse check routine, a runtime routine, and a result domain. The function name must be a valid SQL_IDENTIFIER. The parse routine is the name of an M routine that checks the validity of the input values. The runtime routine is an M routine that computes the value of the result. The result domain is a domain that corresponds to the result value.

A site-defined function can be used anywhere a KB_SQL function can be used. For example, consider how you might use a function for AGE calculation.

Function: AGE(Date_of_birth,comparison_date) SELECT name, sex, AGE(birth_date,today) FROM patients

(55)

Parse Check Routine

The purpose of the parse check routine is to determine if the function reference is valid (e.g., the correct number of arguments is specified). The parser will decompose the function into a set of internal structures. Although it is possible for the parse check routine to determine the composition of each expression, the parse check routine is most useful because it can determine if the correct number of arguments was specified.

The M routine SQL0FC is available for your use. It contains the following subroutines that will check for a required number of arguments and return an error message if not correct.

(56)

SQL0FC - Function parse check routine

TEST(N,M)

where N, M are both integers 2 integer arguments required I2 TEST(A,B,C,D) 4 arguments required A4 TEST(A,B,C) or TEST(A,B,C,D) 3-4 arguments A34 TEST(A,B,C) 3 arguments required A3 TEST(A,B) or TEST(A,B,C) 2-3 arguments A23 TEST(A,B) 2 arguments required A2 TEST(A) or TEST(A,B) or TEST(A,B,C) 1-3 arguments A13 TEST(A) or TEST(A,B) 1-2 arguments A12 TEST(A) 1 argument required A1 Example Description Tag

(57)

IMPORT METHOD EDIT Option

KB_SQL allows you to populate tables using the INSERT command. This command can be used to read data using a particular import format. KB_SQL provides you with a set of standard formats for importing data. (Information on using these standard formats is provided in the KB_SQL Syntax Guide.) However, you may need to import data that is not in one of these standard formats, or you may want to import data from another source other than a host file. For these instances you can create your own custom import method using the IMPORT METHOD EDIT option.

If you have not defined any import methods, the Add window will appear. Select YES to add an import method. Otherwise, if import methods do exist, the Select, Insert, Delete selection window will appear. You can press [insert] to add an import method, or select the import method you wish to edit. If you want to delete an import method, highlight it and press [delete].

(58)

Import Method

Name: character (30) [list]

The import method name can be referenced in a SQL statement that inserts rows from a file using a custom format.

Provide a description for the import method.

Prompt for file? YES/NO

By default, the system will ask for a filename to retrieve the data. Answer NO if your import method does not require a filename. Show rows inserted? YES/NO

By default, the system will display the number of rows inserted into the table. Answer NO if you wish to suppress this message.

Must run in foreground? YES/NO

If this prompt is set to YES, any query that uses this method will not be allowed to run in the background.

After you supply the values in this window, you will be prompted to enter logic to start the process, execute the transfer, and terminate the process.

Note: The code in the following windows is sample code as it applies to our FROM_GLOBAL example.

(59)

Pre Process Execute

This execute initializes a row counter variable (SQLACTR) and determines the maximum number of rows (SQLAMAX) that are defined in the global. Notice that the variable SQLFILE is used as a subscript in the transfer global. (This code is for illustrative purposes only.)

Row Execute

The row execute will increment the counter variable and return the row variable. (This code is for illustrative purposes only.)

Note: When importing from a text file, it is possible to have a null row in the middle of a file (not necessarily at the end). The INSERT query will continue to process after receiving a ROW=null, and will stop only when it receives a SQLERR=437 (end-of-file error). The Row Execute logic must set the SQLERR=437 when the end-of-file occurs.

(60)

End of File Check Execute

This prompt can contain an M execute that checks if the end-of-file condition has been met. If the condition is met, the prompt will set SQLERR="End of File Reached". The end-of-file condition means all of the rows have been processed and the statement has successfully completed. The sample check below is automatically setup for all existing import methods and is backwards compatible.

Post Process Execute

This execute sets a date and time stamp in the transfer global. (This code is for illustrative purposes only.)

(61)

PSEUDO COLUMN EDIT Option

A pseudo column is an M expression that has an associated SQL key word and domain. We refer to them as “pseudo” columns because they are either a constant or a single column belonging to a global which isn’t mapped as a table. The global isn’t mapped as a table because it contains only one record.

Pseudo columns are used so that queries can reference information that does not depend on a particular row (e.g., system constants such as today’s date). This allows the expression to be referenced by the key word in SQL commands. The expression may be any valid M

expression. The key word must be a valid SQL_IDENTIFIER and must be distinct from all other key words. The domain indicates the format of the value returned by evaluating the expression.

KB Systems provides you with several pseudo columns so you can obtain system constants such as the job ID, the user group name, today’s date. To view these default pseudo columns, press [list] from the text area in the SQL text window and select PSEUDO COLUMN. The pseudo column report (CONFIGURATION/ REPORTS/

PSEUDO COLUMN PRINT) produces a list of the pseudo columns that you have defined.

(62)

Add

If you are trying to do something in KB_SQL that you know can be done easily in M, try using a pseudo column. The pseudo column is a symbol representing some M expression, key word, or function. After you define a pseudo column, it can be referenced anywhere a column name can be used.

To create a new pseudo column, enter the name in the Pseudo Column Name window. The Add window will appear after you press [enter]. Select YES to add the pseudo column. Or, you may press [list] from the Pseudo Column Name window to display existing function names in the selection window, and then press [insert] to add a new function.

Pseudo Column Name

Edit

If you want to edit a pseudo column, enter the name in the Pseudo Column Name window and press [enter], or press [list] to view a list of all pseudo columns. Highlight the pseudo column that you want to edit and press [enter]. Consider the effect of changing the definition of the pseudo column. You should review all queries that use the pseudo column to determine if the code will be adversely affected.

Delete

If you want to delete a pseudo column, highlight its name in the selection window and press [delete]. Any query that references the pseudo column will need to be modified.

(63)

Pseudo Column Information

You can define a pseudo column for any commonly referenced M expression. Typical examples include the process number, current directory, and principal device identifier. One of the benefits of pseudo columns is that they enable separate sites to run the same query. In this example, the same query is being distributed to various states

throughout the country. You must create a pseudo column which contains the state code for the current state. Each state receives a different global from which the value of the pseudo column is obtained.

Name: character (30)

Each pseudo column must have a name to identify it within the SQL syntax. The name must be a valid SQL_IDENTIFIER.

Description: character (60) Provide a description for the pseudo column.

Domain: character (30) [list]

Enter a domain to determine how the result value of the pseudo column is to be stored.

(64)

Length: integer (3)

The maximum length for the result of the pseudo column. This prompt will be asked only for those domains with variable length. It will not be asked for values of DATE, TIME, or FLAG domains.

Scale: integer (1)

For numeric data types, you must also enter the number of integers to the right of the decimal point.

M Expression: character (30)

The M expression is the M code that returns the pseudo column value. For our example, the ^WHO global contains customer profile

information. The second piece of the ^WHO global is the state abbreviation.

(65)

SITE EDIT Option

Use this procedure when you first install KB_SQL or after you install a new version of the product. Some of the changes made through this procedure will not be evident until you exit and restart the system. Some of the parameters are used when users first log on to KB_SQL. Other parameters help to enforce the standards and conventions of the organization.

Site Name

Site name: character (30)

The site name appears in the top center of your screen. The site name can be referenced in queries using the SYS_NAME parameter.

(66)

Site Options

ADDRESS INFO Option

The pseudo columns for the system name and address are available for reference in user queries. The system name will appear on the top line of the display for all interactive procedures. Each line of the site address can be referenced as a pseudo column. These columns as well as the site name column, SYS_NAME, are often referenced in the header of user queries.

(67)

Site Address Information

Address line 1: character (40) The first line of the site address (e.g., 10000 South Michigan Ave). Address line 2: character (40) The second line of the site address (e.g., Suite 500).

Address line 3: character (40) The third line of the site address (e.g., Lansing, MI 48917). Address line 4: character (40) The fourth line of the site address (e.g., (512) 333-0201).

Four lines are made available to accommodate lengthy addresses. Be sure to include the full street address, city, state, and postal code. The fourth line is sometimes used for the site phone number.

(68)

DEFAULT INFO Option

KB_SQL is designed to allow you to share data between multiple systems, including M and other applications. The default export and import functions use standard host file formats for the transfer of data. These facilities run on all implementations of M across all hardware configurations. If your M runs in a stand-alone environment, you can still take advantage of the export and import facilities. Simply define a default file format, an export method, and an import method that use globals instead of host files.

Site Default Information

Default file format: character (20) [list] This default file type for host operating system files is used if you do not specify a file type using the FORMAT parameter. Refer to “Format” and “Parameters Within a Set Statement” in the KB_SQL

(69)

Delimiter: character (3)

This value is used to delimit columns in the query result when you save the query results to a data file using the DELIMITED format. Enter this value as the ASCII value of the character desired. For example, to delimit using Control-A, specify 1 as the delimiter. This will place a $CHAR(1) between columns in the result set.

Export method: character (30) [list] This export method is used instead of the default format for the file type being used in the SELECT command.

Import method: character (30) [list] This import method is used instead of the default format for the file type being used in the INSERT command.

Date output: character (20) [list] You can specify the default display format for date values. The list includes those output formats for the DATE data type that are supported by the KB_SQL date conversion utility. Refer to Chapter 11: Miscellaneous Interfaces for more details on the SQL0DT routine. Date input: character (3) [list] You can also specify the default input format for dates. KB_SQL supports the United States, European, and Canadian formats for date inputs. Be sure that your output and input formats are consistent. That is, if you select DMY as an input format, you should display dates with the day first, followed by the month and year. Otherwise, the date conversion utility will report errors in your date input values. Time output: character (20) [list] You can specify the default display format for times. The list includes those output formats for the TIME data type that are supported by the KB_SQL date conversion utility. Refer to Chapter 11: Miscellaneous Interfaces for more details on the SQL0TM routine.

(70)

Moment separator: character (5) KB_SQL supports a MOMENT data type that is a time stamp,

complete with date and time. The format of these values is determined by the output formats specified for dates and times. The date is

separated from the time by the '@' character. You can specify another character string if you wish. For example, to have dates display as '03/30/61 at 10:30 AM', you would specify ' at ' as the moment

separator. This has implications on how your moment input values will be processed. Refer to Chapter 11: Miscellaneous Interfaces for more details on the SQL0TS routine.

Moment time first? YES/NO

By default, KB_SQL displays the date portion first, followed by the separator, then the time portion. Answer YES to reverse this order. This has implications on how your moment input values will be processed. Refer to Chapter 11: Miscellaneous Interfaces for more details on the SQL0TS routine.

Default for COMMIT prompt? YES/NO

This determines the default value for the Commit ? prompt. Enter NO to force the user to enter YES to save the changes.

Show search/select statistics? YES/NO

Answer YES to see the number of records searched and selected for each query run. Compare this value to the access plan cost estimate. Show data access plan? YES/NO

Answer YES to see the data access plan prior to the building routines during query compiles. This provides pseudo code for the query process.

Auto select if one match? YES/NO

Answer YES if selection windows should pick a single matching entry without user confirmation. Answer NO if you wish to confirm all selections even if there is only one match.

(71)

Perform ROW commit? YES/NO

Answer YES if you want to allow INSERT, UPDATE, and DELETE commands to skip rows that contain errors. Otherwise, answer NO. The ANSI SQL standard is to discard all changes and return an error message should an INSERT, UPDATE, or DELETE encounter an error. By answering YES to this prompt you ignore this standard at the site level, allowing the commands to reject any rows with errors and process the remaining rows. The site level setting may be overridden in the SQL Editor by setting SET ROW_COMMIT to either YES or NO. Show DBA status window at startup? YES/NO

A YES answer will display the System Status window each time a DBA or MGR user logs on to the system. Answer NO if you do not wish to display this screen at startup.

Default SearchPatternEscape character: character (3)

This is a metacharacter used by ODBC SQL Catalog functions. It must be a printable character or an ASCII value.

Password expire days: integer (3)

Enter the default number of days after which passwords will expire. This number will be used to set the expiration date for user passwords at time of creation and on password renewal.

Password warning days: integer (3)

Enter the default number of days after which the user will be warned of an upcoming password expiration date. The default is 14 days.

Note:

1.) Use of these password features is completely optional. If you do not want passwords to expire, do not enter anything in the field.

2.) The User Group Member List report shows the expiration date for users that have the value set.

(72)

Site ANSI Information

Return null for expressions that contain null? YES/NO Answer YES if any expression that includes null should return a null result. Answer NO if null values should be treated using the M interpretation of null as the empty string.

Include null values in group and order clauses? YES/NO The result of a query with an ORDER BY or GROUP BY clause is a table with the primary keys specified in the clause. If any key value is null, the associated row will not be included in the result. Answer YES if null values should be included.

Sort nulls as ASCII value: integer (3) If null values are included in the ORDER BY or GROUP BY clauses, they will normally be grouped together at the top or bottom of the result. Use the value 127 (rub out) to have null entries last, 32 (space) to have null entries first.

System delimiter ASCII value: integer (3) If your site has an integrated data dictionary, you may use a standard delimiter. If so, the global mapping process can be greatly accelerated. For example, if all of your globals use the tilde character (126) as the delimiter, enter 126 for the system delimiter. When mapping globals, all piece references can be entered as integer values to be interpreted as the n-th piece of the data.

(73)

Maximum length for SQL identifiers: integer (2) The ANSI standard for SQL states that the maximum length of the SQL identifier is 18 characters. KB_SQL will support an identifier of up to 30 characters in length. Consider readability versus efficiency of input.

Maximum length for sort keys: character (60) This is largely dependent on your M implementation. If your M implementation has a significant limitation on the length of array subscripts, enter that length here. The truncation may cause some variations in result sorting. For example, if you sort by a long

description field, the sort key value will be the first n-characters of the description.

Default transaction isolation level: integer (1) Use this prompt to indicate which isolation level to use: 0 (zero) for READ UNCOMMITTED, 1 for READ COMMITTED, or 2 for REPEATABLE READ. These levels are explained in the KB_SQL

Programmer’s Reference Guide. This value can be overridden by the

(74)

M INFO Option

Site M Information

Default global name: character (10) The M global prefix for all tables created via the CREATE TABLE command. This global name will be used for any table created for a schema where the global prefix is not defined.

Default base routine: character (10) [list] The base routine that is used for the compiled query routines. This base routine will be used for queries compiled by members of any user group where the user group base routine is not defined.

Host file end-of-row terminator: character (10) Enter a string of characters to indicate the end of each row of data output to host files. The default value will be the ! string.

Max routine size: integer (5)

This is largely dependent on your M implementation. A safe estimate to use is 90% of the largest routine size allowed by your

implementation of M. Consider portability issues versus optimum performance.

(75)

Max global data length: integer (10) Enter an integer value that represents the maximum number of characters that may be stored as a data value in a global.

M Functions? YES/NO

If compiled query routines can take advantage of the $FNumber, $Get, $Query, and $Name M functions, answer YES. If you answer NO, the compiled routines will simulate the function.

M Commands? YES/NO

Answer YES if your M implementation supports the Merge command and the Set $Extract(...)=value syntax. Otherwise, answer NO. Protected variable list: character (65)

Enter a list of variable names separated by commas. These variables will be preserved in all kill and exclusive kill commands. We will not kill these variables on entry or exit of KB_SQL. However, we cannot ensure that these variables will not be used internally by KB_SQL. Therefore, we recommend that your list of variables be in the user-defined range of SQLA* variable names.

Routine alter/save execute: character (65)

Use this prompt to call a program. The program will affect the M code that KB_SQL generates from your queries. The program will be executed after KB_SQL resolves all soft tag references and other compile time variables. You will receive a local array to which you can add code and then save. If you take responsibility for saving the

routine, you must kill the local array before returning to the caller. If you encounter an error, you should return the SQLERR variable. If you don’t save the routine, KB_SQL will save it using the logic in SAVE^SQL0RI. You are responsible for the format of any lines that you insert in the local array. All routine lines are of the form:

[tag]<space><Mcode>.

The routine array is in the format: RTN=Name of routine

(76)

DATE/TIME LIMITS Option

Site Date/Time Limits

Delete transaction logs after:___ days integer (2)

Enter the number of days that the old transaction log data will be available for review. Typical values will be 7-28 days. As DBA, you should balance storage costs against the benefit for review.

Log-off users after: ___ seconds integer (5)

Enter the number of seconds to wait for a user’s response. Typical values will be 600-1200 seconds (10-20 minutes). If no key is pressed, the system will respond as if the user pressed the [undo] key.

Timeout in SQL Editor after: ____seconds integer (5)

Enter the number of seconds to wait before the SQL Editor times out. When the SQL Editor times out, it acts as if you pressed [undo].

KB_SQL Database Administrator s Guide Version 4

KB_SQL

Database

Administrator’s

Guide

Table of Contents

Preface

Purpose

Audience

Conventions Used in this Manual

The Organization of this Manual

Error Messages

Additional Documentation

An Overview of the KB_SQL

User Groups and Menus

The Database Administrator (DBA)

SHARK: The DBA Password

The DBA Menu

Select DBA OPTIONS

Select CONFIGURATION

DATA DICTIONARY Option

Select DATA DICTIONARY

TERMINALS/PRINTERS Option

Select TERMINALS/PRINTERS

SECURITY Option

Select SECURITY

UTILITIES Option

Select UTILITIES

SYSTEM STATUS Option

VERSION INFORMATION Option

The DBA’s Responsibilities to the End User

Tutorial Issues

The User Profile Checklist

The System Manager (SysMgr)

MGR: The SysMgr Password

The System Manager Menu

Select SYSTEM MANAGER OPTIONS

CONFIGURATION Option

DATA DICTIONARY Option

TERMINALS / PRINTERS Option

SECURITY Option

UTILITIES Option

SYSTEM STATUS Option

VERSION INFORMATION Option

The User

The USER Password Menu

The EZQ Password Menu

The RUN Password Menu

Essential Globals and Routines

The Essential Globals

The Essential Routines

The Demonstration Globals

Configuring KB_SQL for Your Site

BASE ROUTINE EDIT Option

Add

Edit

Delete

Base Routine Information

Sample Configuration

Routine Name Assignment

EXPORT METHOD EDIT Option

Export Method

Pre Process Execute

Row Execute

End of File Check Execute

Post Process Execute

FUNCTION EDIT Option

Function Name

Add

Edit

Delete

Function Information

Compile-time M Code

Runtime Routine

How to Use a Function

Parse Check Routine

IMPORT METHOD EDIT Option

Import Method

Pre Process Execute

Row Execute