Did you ever wonder how your PC-side client applications, such as Client Access/400 or Visual Basic, communicate their requests for data to the AS/400? Ever wonder how the AS/400 processes those requests? It’s all managed by a set of database server programs on the AS/400 that provide client applications with access to the powerful AS/400 DB2/400 database. These AS/400 server programs provide access to the full scope of database functions required by client applications, including dynamic SQL support; catalog, or “list” retrieval services; and native AS/400 database functions. As if that weren’t enough, these functions are also accessible via a variety of communications protocols, such as TCP/IP and SNA. This not only adds more AS/400 functionality, but it also adds an additional layer of complexity. Because of the business-critical support provided by the AS/400’s database server programs, understanding the pieces and how they fit together is important. This article shows you how everything works together to serve your AS/400 data to your PC clients. We also provide a few useful techniques for monitoring, configuring, and troubleshooting your client-to-AS/400 connections.
Looking for a Job?
Depending on the type of connectivity, you use different AS/400 server programs as the communications interface for your applications. Figure 1 shows you what those programs are.
What do these OS/400 servers mean to you? They mean that when you run into client/server problems, you can search the AS/400 for active jobs with these names. If you want to track down a user job running TCP/IP with Secure Sockets Layer (SSL), for example, use the Work with Active Jobs (WRKACTJOB) command (shown in Figure 2) to look for a QZDASSINIT job servicing the user’s profile name. You should be aware that the subsystem name the job is running under may vary, so be sure to look through the complete WRKACTJOB list to find the job in question. In general, the database server programs usually run in the QSERVER subsystem, while some SNA jobs using QPCSUPP mode can run only in the QCMN subsystem.
A helpful hint is that the WRKACTJOB display shows all QZDAxxINIT jobs running under the same user profile, that is, QUSER. This is the initial user profile used
while the prestart job is in the prestart wait (PSRW) status. The user profile of the person actually connecting from the client is shown only in the job log as the user profile being
“serviced.” Starting in OS/400 V4R3, the name of the client PC is also included in the job log. This can be helpful when several different clients use the same standard user ID. If different user profiles are used with your QZDAxxINIT jobs, you can issue the Work with Object Locks (WRKOBJLCK) command to list the QZDAxxINIT jobs being locked, or used, by your target user. Here’s an example:
WRKOBJLCK OBJ(user_name) OBJTYP(*USRPRF)
A Friend When You Need One!
The QSERVER subsystem is configured to use prestart jobs to improve the performance of the database server jobs (and also for the other host server jobs, such as the data queues server or the file server). Compared to regular batch jobs, prestarted jobs eliminate the time spent waiting for job initialization when a client connects to use a database service. Time is also saved during a client’s disconnect because the job usually does not end completely. Instead, the current connection information gets cleared, and the job will be “reused” for a future connection request.
Because these jobs are started prior to the actual connection request from the client, the job start time displayed in the first job log message may be quite different than the time of the actual connection. To get more accurate time stamp information, look at the individual messages displayed in the job log.
To display the current AS/400 server prestart job settings, use the Display Subsystem Description (DSPSBSD) command with a parameter of SBSD(QSERVER) and choose the Prestart job entries option from the Display Subsystem Description screen. You can then display the details for a specific QZDAxxINIT job entry by using option 5=Display details on the Display Prestart Job Entries screen that appears.
To modify your prestart job settings, the Change Prestart Job Entry (CHGPJE) command allows you to adjust specific prestart job settings, such as these:
• MAXUSE controls the number of times a job is reused by different connections. When the job reaches the maximum number of reuses, the job terminates, and a job log will be written for user QUSER. The job log contains a message of “CPC1210—Prestart job ending due to maximum use....” The job log also will contain a message of
“CPIAD07—Host server error occurred with reason code 7.” This is not an error message but a notification that the job ended because the maximum reuse threshold was reached.
• ADLJOBS controls the number of initial prestart jobs to start in the background.
• STRJOBS controls whether or not prestart jobs are automatically started when the subsystem starts.
To achieve optimal performance, the parameter for the number of initial prestarted jobs should be set to the number of concurrent users you expect to access a specific QZDAxxINIT database server program.
To check if there are prestart jobs waiting to service a client request, use the WRKACTJOB SBS(QSERVER) command and the F14=Include to show the PJ (prestart) type jobs that are in the PSRW status.
Where Is the Work Really Done?
Despite the slight differences in the names of each communication layer server program, they all route the actual database requests to the same underlying layer of programs. As shown in Figure 3, this functional layer consists of four programs that format a client’s
request into a request that is optimized for use by OS/400. Under normal conditions, the database server programs are invoked transparently without any need for further user actions. To ensure that your AS/400 setup is correct, check the following:
• The OS/400 Host Servers option (option 12 of product xxxxSS1, where xxxx is the license program product number for the current version of OS/400 installed) is installed.
• The proper communication protocols and subsystems are configured and running on the AS/400.
• The prestart database jobs for the specific protocol are running.
• The client can connect to the AS/400. AS/400 Client Access Express for Windows and Client Access/400 for Windows 95/NT offer a command line tool, CWBPING, and a GUI version to perform connection verification. CWBPING is valid for only TCP/IP and IPX connections, and you find it in the directory in which Windows is installed. The GUI version is part of Operations Navigator, and you run that version by selecting an AS/400 and selecting Verify connection from the File menu or Context popup menu.
The Display Software Resource (DSPSFWRSC) command will show if the OS/400 Host servers option is installed. The Host Servers option can be seen in Figure 4.
We’ve already covered the fact that the QZDAxxINIT jobs run in the QSERVER subsystem, but, for TCP/IP and IPX connectivity, you also need the QSYSWRK subsystem to be active. The Start TCP/IP (STRTCP) and Start IPX (STRIPX) commands will start the proper protocols, while the Start Subsystem (STRSBS) command can be used to start the necessary subsystems. Furthermore, you should verify that the jobs QSERVER/QZDASRVSD server daemon and QSYSWRK/QZSOSMAPD are running. Run the Network Status (NETSTAT) command with the Connection status option as follows:
NETSTAT OPTION(*CNN)
The NETSTAT command should also show the following ports in the “Listen”
state:
• As-cent[ral] is used for licensing purposes.
• As-signon is used for user ID and password validation.
• As-svrmap is used for finding the server’s socket (database’s socket, in our case).
• As-data[base] is used for database server requests.
If the servers are not started, use the Start Host Server— STRHOSTSVR SERVER(*ALL)—command to start all of the host servers. Single host servers can also be started with this command. The full list of host servers is file, data queue, network print, central, signon, and remote command/distributed program call. The central server and the signon server support the database server and other servers by providing functionality that is common between the host servers. As we mentioned before, the CWBPING tool will also show which of these servers are active.
Who Uses the Database Server?
The list of client applications that take advantage of the database server’s function continues to grow; however, the main users of the database server can be found in IBM’s Client Access/400 product. The individual Client Access functions that use the database server are the ODBC driver, data transfer, Operations Navigator, the OLE DB provider, and the optimized SQL APIs. In addition to these users, IBM’s Java Toolbox Java Database Connectivity (JDBC) driver also uses the database server. But don’t be fooled into thinking these are the only applications that access the database server. To allow other client-side software vendors to gain access to AS/400 database functions, IBM has made the Database Server Licensed Internal Program Interface (LIPI) available. This means that any number of non-IBM developed applications can use the database server for their particular database needs. The Database Server LIPI may also be useful for debugging problems on your own. If you know how to take a communications trace, you can use the LIPI documentation to determine the point of failure of an application’s interaction with the database server.
How to Control Server Access
To monitor requests that a client sends to the server, you can register exit programs to IBM-provided exit points via the Work with Registration Information (WRKREGINF) command. These programs will receive a block of the parameters being used for the current client request, and the program can decide whether to allow this request to be handled by the server or to block the request. You could also choose to log certain requests or trigger further actions depending on the actual content of the parameter block.
The five exit points available for the database server correspond to the single database server programs mentioned in Figure 3. Each exit point is listed and defined in Figure 5.
More information on the database server, including these exit points, can be found in the IBM manual Client Access Express Host Servers (SC41-5740).
You’re Ready!
You are now armed with the information that you need to be more comfortable with serving data from your AS/400. In this article, we’ve covered the wide range of information you need to set up, configure, and run the database server function. We also covered the important functions of problem determination (by looking at the job log), restricting access to server functions using exit points, and performance tuning using prestarted jobs and job reuse. Finally, we covered the potential users of the database server. Knowing the list of potential users can help you decideif you need to take extra precautions to protect private or classified data.
References and Related Materials
• AS/400 Client Access Express for Windows APIs Information (SC41-3513-04)
• AS/400 Client Access Express for Windows: Implementing V4R4M0, Redbook (SG24- 5191-00)
• Client Access Express Host Servers V4R4M0 (SC41-5740-03)
• Inside AS/400 Client Access for Windows 95/NT V3 R1 Mod2, Redbook (SG24-4748-
01)
• Inside Client Access/400 for Windows 3.1 V3R1M1, Redbook (SG24-4429-02)
• The Client Access home page: www.as400.ibm.com/clientaccess/
• The Java Toolbox home page: www.as400.ibm.com/toolbox/
QIWS/QZDAINIT Used for SNA (802.2) and Anynet connections.
QIWS/QZDASOINIT Used for TCP/IP and IPX socket connections.
QIWS/QZDASSINIT Used for secure TCP/IP and IPX socket connections
(requires installation of additional host and client SSL Software).
QIWS/QZDACMDP Provides general functions of space management and request routing to the following three programs.
QIWS/QZDASQL Handles SQL requests, such as Prepare, Execute, and Fetch.
QIWS/QZDAROI Retrieves object information, such as special columns, indexes, and package statements.
QIWS/QZDANDB Interfaces with the native database. Deals with nonstandard items, such as source physical files, multiple members of files, and library lists.
Figure 3: These programs make up the functional layer of a client’s request for DB2/400 data.
Figure 1: These database server programs run on the AS/400.
Figure 2: Use the Work with Active Jobs (WRKACTJOB) command to track down a user job.
Figure 4: Use the Display Software Resources (DSPSFWRSC) to verify the Host Servers option is installed.
QIBM_QZDA_INIT Calls exit program at server initialization time.
Exit point for QZDACMDP program.
QIBM_QZDA_SQL1, Calls exit program for SQL operations, such as QIBM_QZDA_SQL2 Prepare, Execute, and Fetch. SQL2 should be used when the SQL statement is longer than 512 bytes. Exit points for QZDASQL program.
QIBM_QZDA_ROI1 Calls exit program for object information retrieval and SQL catalog functions. Exit point for QZDAROI program.
QIBM_QZDA_NDB1 Calls exit program for native database functions, such as working with members and creating/ deleting files. Exit point for QZDANDB program.
Figure 5: These are the five exit points available for the database server.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online