• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Harnessing Your ODBC Users with Exit Programs

    November 29, 2006 Bob Butcher

    I check my daily Web logs to see what intranet Web applications are being used. The log gets updated on the AS/400 and provides a great tool for our auditors. I came in one morning and the log was empty. I thought that was funny because we normally get 175 to 200 entries per day. I checked the log throughout the day on that particular day and there were many entries of usage. The next morning, it was empty again. This was happening every morning when I came in. Because we do a lot of client server applications using ODBC, I wondered if there was a way to track what was happening each morning around 7 a.m.

    That is when I discovered exit programs on the AS/400 (which obviously also exist on the iSeries and the System i5, which both run i5/OS and OS/400, but we have an AS/400 where I work, so I call it that). An exit program can provide an extra layer of security, in addition to OS/400 security, that can help control user access to applications. When a user requests to use a particular server application, it exits first to the exit program, to see if they are authorized to proceed, or if they are rejected from continuing on to access data. The exit program that I used on the AS/400 to work with ODBC was QIBM_QZDA_INIT.

    All ODBC traffic works it way through the QIBM_QZDA_INIT exit program. I decided to create my own exit program to monitor all of the traffic to try and find the person who might be affecting my Web logs. The program I created below extracts the profile name of the user who is trying to access the AS/400, and sends a message to my user profile. The message will have the time and date that it was sent. Below is the snippet of code on how this works:

    PGM        PARM(&STATUS &REQUEST)  
                                                             
     DCL        VAR(&STATUS) TYPE(*CHAR) LEN(1) /* ACCEPT OR 
                  REJECT THE USER TRYING TO ACCESS AS400 */ 
     
     DCL        VAR(&REQUEST) TYPE(*CHAR) LEN(34) /* + 
                  PARAMETERS STRUCTURE */                    
                                                             
                          /* PARAMETERS DECLARES */  
                                  
     DCL        VAR(&USER) TYPE(*CHAR) LEN(10) /* USER +     
                  CALLING SERVER VIA ODBC */ 
                    
     DCL        VAR(&SRVID) TYPE(*CHAR) LEN(10) /* DATABASE +
                  SERVER VALUE - *SQL  */ 
                       
     DCL        VAR(&FORMAT) TYPE(*CHAR) LEN(8) /* FORMAT +  
                  NAME - ZDAI0100 */ 
                            
     DCL        VAR(&FUNC) TYPE(*CHAR) LEN(4) /* FUNCTION +  
                  BEING PERFORMED - 0 */                     
                                                             
        /* EXTRACT PARAMETERS FROM THE &REQUEST FIELD */    
        
     CHGVAR     VAR(&USER) VALUE(%SST(&REQUEST 1 10))  
     CHGVAR     VAR(&SRVID) VALUE(%SST(&REQUEST 11 10)) 
     CHGVAR     VAR(&FORMAT) VALUE(%SST(&REQUEST 21 8)) 
     CHGVAR     VAR(&FUNC) VALUE(%SST(&REQUEST 28 4))  
                                                               
     /* STATUS FIELD OF 1 WILL LET USER CONTINUE, CHANGE TO */ 
     /* 0 TO REJECT ACCESS TO AS400                         */  
         
     CHGVAR     VAR(&STATUS) VALUE('1')                    
                                         
     /* SEND MESSAGE TO ME WITH WHO HAS ACCESSED AS400 VIA ODBC */  
     
     SNDMSG     MSG(&USER *CAT ' has accessed the AS400 +  
                  through ODBC.') TOUSR(BOB)                   
                                                               
     ENDPGM     
    

    A couple of key points to make are that the parameters &STATUS and &REQUEST are required and need to be defined the way that they are in the declaration statements. More information regarding detail information about this exit point and the necessary parameters and exit program format details can be found on the IBM Web site. Note the value of the &STATUS variable. If this value is 1, the user can continue to the AS/400. If the &STATUS variable is 0, the user has been rejected and cannot continue to the AS/400. The &USER field is extracted from the &REQUEST field and this is the user profile of who is trying to get at the AS/400.

    To get this program up and running on my system, I had to follow the following steps:

    1. Compile the program
    2. Type WRKREGINF (work with registration information) at a command line
    3. Select option 8 to work with exit programs
    4. Select option 1 to add the program that was just compiled

    This exit program solved my problem. I had a co-worker from IT, who came in every morning, fired off a VB program at 7 a.m. and accidentally cleared the wrong file member. I sifted through the messages that were being sent to me in the morning, looked at the user profiles, and determined who to ask what they were running in the morning. Without this exit program in place, it would have been difficult to figure this out.

    This sample is a very simple one. You could add more complexity to your program to accept/reject users. Not only do exit programs work for ODBC, but they work for other applications that request access to AS/400 information, such as FTP, DDM, and TELNET. Writing your own exit programs can get to be very complex and there are several great exit point software packages out there today. These companies have done all of the program creation for you and have the auditing tools built in their software already. This sample of code above should at least get your feet wet in understanding a little bit about exit programs.

    Bob Butcher is a software consultant with over 19 years experience in the AS/400 world. He specializes in client/server applications that deal with the AS/400 and Active Server Pages or Visual Basic. He can be reached at bbutcher@samicsoft.com.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Integrated Print Solutions:  Print AFP/IPDS documents to any network printer
    COMMON:  Join us at the Annual 2007 Conference & Expo, April 29 - May 3, in Anaheim, California
    Canvas Systems:  We build and deliver custom iSeries rental solutions

    Seagull Software Shows Growth in First Half of Fiscal 2007 The System iWant, 2007 Edition

    Leave a Reply Cancel reply

Volume 6, Number 42 -- November 29, 2006
THIS ISSUE SPONSORED BY:

SEQUEL
IBS
Centerfield Technology

Table of Contents

  • Powerful Stuff: Creating Commands with Variable-Length Parameters!
  • Harnessing Your ODBC Users with Exit Programs
  • Using Different ODBC DSN Types for i5 Access

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle