home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMOFLS.ZIP
/
DBDEMO.DOC
< prev
next >
Wrap
Text File
|
1991-08-02
|
15KB
|
389 lines
DATABASE PERFORMANCE CONCEPTS AND TECHNIQUE DEMONSTRATION PROGRAM
USER DOCUMENT
You can install this demo in one of two ways. You can install the
executables or you can compile the source from scratch. There are
slight differences in the procedure for each method.
DEMO INSTALLATION REQUIREMNTS
TO INSTALL THE EXECUTABLES ON THE SERVER (WITHOUT COMPILING)
1. Create an ID name (BMCHUGH) with System Admin Authority.
Logon with ID name BMCHUGH.
2. To create the Bank Database make the current directory the directory which
contains the database build programs (REF: README file on diskette).
Unpack the Demo Database Files (DEMODB.EXE), then Type DBCREATE <enter>.
This will create the BANK database.
3. Unpack the demo files (DEMOFLS.EXE) in a separate directory and make that
the current directory. (REF: README file on diskette)
4. Run the command "SQLBIND ARIS.BND BANK"
5. Copy the file ARIS.DLL to the directory "\sqllib\dll" on the server.
6. If you want to run the demo on the server go to step 7. If you want
to run the demo from a requester (recommended) go to step 10 in the
section (RUN DBDEMO FROM A REQUESTER :) below.
7. Type DEMOBIND at the server. Now the DBDEMO program is ready to run
at the server. Type DBDEMO to start the demo. (Stop here)
TO COMPILE THE SOURCE CODE AND RUN DEMO
SYSTEM SET UP:
1. A C compiler, with all the relevant environment variables set up in
the config.sys file, i.e. (assuming you are using C: drive )
SET INCLUDE must contain C:\SQLLIB;
SET LIB must contain C:\SQLLIB;
2. IBM OS/2 TOOLKIT should be installed with proper variables in the
config.sys.
3. There should be no database named BANK since that is the name that this
program uses.
CREATE DATABASE: (requires compiling)
4. Create an ID name (BMCHUGH) with System Admin Authority.
Logon with ID name BMCHUGH.
5. To create the Bank Database make the current directory the directory which
contains the database build programs (REF: README file on diskette).
Unpack the Demo Database Files, then Type DBBUILD <enter>.
RUN DBDEMO ON SERVER: (requires compiling)
6. Make the directory containing the demo files the current directory.
(REF: README file on diskette)
7. To create the file ARIS.DLL, type MAKE ARIS, which creates the DLL required
for the ARI funtion. In addition ARIS.DLL is copied to the subdirectory
C:\SQLLIB\DLL. This is valid if the machine you are compiling on
is also the database server. Otherwise, the DLL has to be copied to
the server.
8. To compile the DEMO type MAKE DBDEMO.
9. Now the DBDEMO program is ready to run at the server. Type DBDEMO to
start the demo.
RUN DBDEMO FROM A REQUESTER :
10. Start the communication manager, Start the database manager,
and catalog the BANK database in Query manager.
11. Create a directory on the requester for the demo files.
12. To run the DBDEMO program on a requester the following files
are required from the server ( .exe, .txt, .bnd,). These files
can be copied to diskette by typing CPYREQ at the server. The files
must then be copied to the requester directory created in step 11.
13. Type DEMOBIND at the requester. Now the DBDEMO program is ready to run
at the requester. Type DBDEMO to start the demo.
This program, written by the OS/2 EE Application Development Technical Support
group in Austin, Texas, illustrates some techniques used to get better
performance out of OS/2 EE Database manager.
The program consists of a Presentaion Manager interface and C language
modules containing embedded SQL statements. A table containing all the
source modules, header files, text files, make and link files is shown
below (appendix A).
The database used simulates a banking environment and is named BANK.
The table layouts and other information about this database are shown
in appendix B.
All the programs were compiled using the qualifier BMCHUGH and should
be changed to your logon ID (search and replace) in all the source
files. Or, you may choose to create the id BMCHUGH on your machine and
leave the demo code unchanged.
All the code should reside on the requester except for the ARI
procedure - ARIS.DLL, which should be copied into a subdirectory on
the server which is in the LIBPATH. (check CONFIG.SYS). All the text
files (.txt) should reside in the subdirectory from which the program
is to be run.
When running demos of ARI, RECORD BLOCKING, INDEXES, or STATIC vs DYNAMIC
SQL you may only have one demo screen active at any given time. (ie. If
you run ARI you must close that screen before running RECORD BLOCKING).
QUERIES USED
Indices
PERFDAT3 and PERFDAT4 are exactly the same tables except
PERFDAT4 has an index on CU_SSN.
SELECT * FROM BMCHUGH.PERFDAT3
WHERE CU_SSN BETWEEN '111-11-1020' AND '111-11-1140';
SELECT * FROM BMCHUGH.PERFDAT4
WHERE CU_SSN BETWEEN '111-11-1020' AND '111-11-1140';
Static vs Dynamic:
The query was designed to be complex (to increase the PREPARE time)
and to access only one row, so that the difference between dynamic and
static query would be clear. However, this DOES NOT imply that
dynamic queries are always slower than static queries. In some cases,
when the database has undergone changes since the last time the application
was bound, dynamic queries can take advantage of the latest statistics
and run faster.
Usually, the choice between dynamic or static query is determined by
application constraints, e.g., when the query is not known in
advance.
SELECT CU_NAME, CU_SSN, S_BAL, C_ACCNUMB
FROM BMCHUGH.CUSTOMER, BMCHUGH.SAVINGS, BMCHUGH.CHECKING
WHERE CU_SSN = S_SSN AND CU_SSN > '000-00-0000'
AND CU_SSN < '999-99-9999' AND S_BAL > 499
AND S_BAL < 20000 AND C_STARTBAL > 0
AND C_STARTBAL < 10000 AND C_ENDBAL > 0
AND C_ENDBAL < 20000 AND C_STARTBAL <= C_ENDBAL
AND CU_ZIP BETWEEN '11111' AND '99999'
AND CU_NAME LIKE 'B%Vald%'
AND (CU_SSN = C_SSN1 OR CU_SSN = C_SSN2);
Record Blocking and ARI
The median value is being found for the column C_ENDBAL
from the table CHECKING. The module noari() uses the SQL
queries:
EXEC SQL SELECT COUNT(*)
INTO :num_rows
FROM BMCHUGH.CHECKING;
EXEC SQL DECLARE CBLOCK CURSOR FOR
SELECT C_ENDBAL FROM BMCHUGH.CHECKING
ORDER BY C_ENDBAL;
Then the cursor CBLOCK is used, num_rows/2 times, to FETCH
the desired median balance.
/************************************************************************/
APPENDIX A
/************************************************************************/
List of source files and functions
/************************************************************************/
/* Source File Modules Includes make/def/link Text Files
/************************************************************************/
DBDEMO.SQC main() DBDEMO.H DBDEMO DBDEMO.IPF
**PM Functions** DBCOMM.H DBDEMO.LNK
DDOCMsgProc() DBDEMODB.H DBDEMO.DEF
DOCPARMSsgProc() DLGDBDEM.H DBDEMO.RC
EDITBOXMsgProc()
SHOWTIMEMsgProc()
WndProc()
SetDlgText()
PMErrChk()
**DB functions**
SQLthread()
StartDB()
StopDB()
/************************************************************************/
STAT_DYN.SQC stat_dyn() DBCOMM.H DBDEMO STAT_DYN.TXT
stat() DB.H
dyn()
/************************************************************************/
INDICES.SQC indices() DBCOMM.H DBDEMO INDICES.TXT
indx() DB.H
noindex()
/************************************************************************/
RBLOCK.SQC rblock() DBCOMM.H DBDEMO RBLOCK.TXT
blocking() DB.H
RBLOCKNO.SQC rblockno() DBCOMM.H
blockin_no() DB.H
/************************************************************************/
ARI.SQC ari() DBCOMM.H DBDEMO ARI.TXT
arir() DB.H
ARIS.SQC get_total() ARIS
ARIS.DEF
ARIS.LNK
/************************************************************************/
GETERR.C get_error() DBDEMO
DELTA.C delta() DBDEMO
/************************************************************************/
DBDEMO.DOC contains this document
The following files contain a list of configuration parameters
which are displayed by the program:
DBMPARMS.TXT contains a table of DATABASE MANAGER parameters
DBPARMSG.TXT contains a table of DATABASE GENERAL parameters
DBPARMSH.TXT contains a table of DATABASE HEAP parameters
DBPARMSL.TXT contains a table of DATABASE LOG parameters
RDSSRV.TXT contains info on installing RDS on a server
RDSREQ.TXT contains info on installing RDS on a requester
/************************************************************************/
APPENDIX B
/************************************************************************/
TABLE=BMCHUGH.CHECKING
Column Name Type Length Attributes
----------- ---- ------ ----------
C_ACCNUMB Character (Fixed) 8 Data required, Text
C_STARTBAL Decimal 9,2 Data required
C_ENDBAL Decimal 9,2 Data required
C_SSN1 Character (Fixed) 11 Data required, Text
C_SSN2 Character (Fixed) 11 Text
TABLE=BMCHUGH.CUSTOMER
Column Name Type Length Attributes
----------- ---- ------ ----------
CU_SSN Character (Fixed) 11 Data required, Text
CU_NAME Character (Fixed) 30 Data required, Text
CU_ADDR1 Character (Fixed) 25 Data required, Text
CU_ADDR2 Character (Fixed) 25 Data required, Text
CU_ZIP Character (Fixed) 5 Data required, Text
TABLE=BMCHUGH.SAVINGS
Column Name Type Length Attributes
----------- ---- ------ ----------
S_ACCNUMB Character (Fixed) 8 Data required, Text
S_IRATE Decimal 5,2 Data required
S_INTTOD Decimal 7,2
S_BAL Decimal 9,2 Data required
S_SSN Character (Fixed) 11 Data required, Text
TABLE=BMCHUGH.PERFDAT3
Column Name Type Length Attributes
----------- ---- ------ ----------
CU_SSN Character (Fixed) 11 Data required, Text
CU_NAME Character (Fixed) 30 Data required, Text
CU_ADDR1 Character (Fixed) 25 Data required, Text
CU_ADDR2 Character (Fixed) 25 Data required, Text
CU_ZIP Character (Fixed) 5 Data required, Text
L_NUMB Character (Fixed) 8 Data required, Text
L_TYPE Character (Fixed) 1 Data required, Text
L_RATE Decimal 5,2 Data required
L_TERM Small Integer Data required
L_PAYMT Decimal 7,2 Data required
L_BAL Decimal 9,2 Data required
L_INTODAT Decimal 9,2
L_DESCRIP Character (Fixed) 25 Data required, Text
L_DATE Date
S_ACCNUMB Character (Fixed) 8 Data required, Text
S_IRATE Decimal 5,2 Data required
S_INTTOD Decimal 7,2
S_BAL Decimal 9,2 Data required
B_NUMB Character (Fixed) 3 Data required, Text
B_SIZE Character (Fixed) 1 Data required, Text
B_RENT Decimal 3,2 Data required
B_PAYUP Character (Fixed) 3 Text
C_ACCNUMB Character (Fixed) 8 Data required, Text
C_STARTBAL Decimal 9,2 Data required
C_ENDBAL Decimal 9,2 Data required
******INDEXES******
Index Name: BMCHUGH.XCU_SSN
Duplicates Allowed: Yes
Column Name Order
----------- -----
CU_SSN Ascending
References Articles and Performance Documemtation:
1. OS/2 EE Database Manager Administrator's Guide, Chapter 7, (S01F-0267).
2. ITSC's OS/2 EE 1.2 Database Services and Query Manager, Cookbook, (GG24-3557).
3. OS/2 EE 1.2 Database Manager Performance, Velasco and Li, IBM Personal
System Developer, issue 1, 1991.
4. Inside IBM's Database Strategy, DBMS supplement.
5. Software Digest Buyer's Alert, vol 7, num. 13, 1990.
6. "Database Application Design Class", IBM video-tapes, Tim Li, Dan Hargrove,
Tim Malkemus, Feb 22 and 23, 1990.
7. Configuring Os/2 1.1 Database Services, IBM FTN video-tapes, May 11, 1989.
8. OS/2 EE Database Manager: Configuration Parameters, Baker et al, IBM
Personal Systems Developer, Spring 1990.
9. The Database Manager Optimizer, pp 74, IBM Personal System Developer,
Summer, 1989.
10. Performance of OS/2 EE 1.1 Database Services, Velasco and Li, IBM
Personal System Developer, Summer, 1989.
11. OS/2 Notebook Best Of Personal System Developer.
12. Client-Server Programming With OS/2 Extended Edition.
Robert Orfali and Dan Harkey
13. DataBase Application Remote Interface.
Lance Amundsen and Nameeta Sappal
14. COOKBOOKS
A. Database Remote Data Services
B. Communication Manager and Database Introduction
C. Database Manager and Database Services and Query Manager
D. Database Manager - User Profile Manager
E. Performance and Capacity Optimization
F. Print Subsystems
15. CUA Documentation
Advanced Interface Design Guide
16. CPI Documentation
Database Reference