AS400 Analyst Programmer
Contract
Immediate contract requirement for an experienced AS400 Analyst to work in a large Bank in Dublin City. AS400 RPG IV, RPGILE and CL Development.
Role:
Write and design technical specifications.
Write and execute unit test scripts, system test strategy and scripts.
Write code changes in RPGILE on development AS400.
Skills:
At least 10 years RPG IV experience.
Previous experience of financial systems, working within the banking sector is required.
If you would like to apply for this role and would like to hear more about this opportunity please email your CV to (see below)
Location Dublin, Ireland
Country Ireland
Start Date ASAP
Duration 6 months
Rate Market rates in Euro.
Employment Business CPL Solutions
Contact Janet Smullen
Email Janet.Smullen.5CF1F.98727@mail.jobserve.com
Reference JS98179
Posted Date 24/03/2010 15:19:04
as400 sbmjob
as400 journalling
Saturday, March 27, 2010
AS400 journalling
AS400 journalling
How many sites use as400 journalling in their applications. Seems to be that a lot of as400 developers dont actually understand journalling and the data integrity and audit capabilities it can add to an enterprise level system.
The DB2 UDB for AS400 journalling support supplies an audit trail and forward and backward recovery. Forward recovery can be used to take an older version of a table and apply the changes logged on the journal to the table. Backward recovery can be used to remove changes logged on the journal from the table.
When an SQL collection is created, a journal and journal receiver are created in the collection. When SQL creates the journal and journal receiver, they are only created on a user auxiliary storage pool (ASP) if the ASP clause is specified on the CREATE COLLECTION or the CREATE SCHEMA statement. However, because placing journal receivers on their own ASPs can improve performance, the person managing the journal might want to create all future journal receivers on a separate ASP.
When a table is created into the collection, it is automatically journaled to the journal DB2 UDB for AS/400 created in the collection (QSQJRN). A table created in a non-collection will also have journaling started if a journal named QSQJRN exists in that library. After this point, it is your responsibility to use the journal functions to manage the journal, the journal receivers, and the journaling of tables to the journal. For example, if a table is moved into a collection, no automatic change to the journaling status occurs. If a table is restored, the normal journal rules apply. That is, if the table was journaled at the time of the save, it is journaled to the same journal at restore time. If the table was not journaled at the time of the save, it is not journaled at restore time.
The journal created in the SQL collection is normally the journal used for logging all changes to SQL tables. You can, however, use the system journal functions to journal SQL tables to a different journal. This may be necessary if a table in one collection is a parent to a table in another collection. This is because DB2 UDB for AS/400 requires that the parent and dependent file in a referential constraint be journaled to the same journal when updates or deletes are performed to the parent table.
A user can stop journaling on any table using the journal functions, but doing so prevents an application from running under commitment control. If journaling is stopped on a parent table of a referential constraint with a delete rule of NO ACTION, CASCADE, SET NULL, or SET DEFAULT, all update and delete operations will be prevented. Otherwise, an application is still able to function if you have specified COMMIT(*NONE); however, this does not provide the same level of integrity that as400 journalling and commitment control provide
as400 sbmjob
as400 prtf report layout utility
How many sites use as400 journalling in their applications. Seems to be that a lot of as400 developers dont actually understand journalling and the data integrity and audit capabilities it can add to an enterprise level system.
The DB2 UDB for AS400 journalling support supplies an audit trail and forward and backward recovery. Forward recovery can be used to take an older version of a table and apply the changes logged on the journal to the table. Backward recovery can be used to remove changes logged on the journal from the table.
When an SQL collection is created, a journal and journal receiver are created in the collection. When SQL creates the journal and journal receiver, they are only created on a user auxiliary storage pool (ASP) if the ASP clause is specified on the CREATE COLLECTION or the CREATE SCHEMA statement. However, because placing journal receivers on their own ASPs can improve performance, the person managing the journal might want to create all future journal receivers on a separate ASP.
When a table is created into the collection, it is automatically journaled to the journal DB2 UDB for AS/400 created in the collection (QSQJRN). A table created in a non-collection will also have journaling started if a journal named QSQJRN exists in that library. After this point, it is your responsibility to use the journal functions to manage the journal, the journal receivers, and the journaling of tables to the journal. For example, if a table is moved into a collection, no automatic change to the journaling status occurs. If a table is restored, the normal journal rules apply. That is, if the table was journaled at the time of the save, it is journaled to the same journal at restore time. If the table was not journaled at the time of the save, it is not journaled at restore time.
The journal created in the SQL collection is normally the journal used for logging all changes to SQL tables. You can, however, use the system journal functions to journal SQL tables to a different journal. This may be necessary if a table in one collection is a parent to a table in another collection. This is because DB2 UDB for AS/400 requires that the parent and dependent file in a referential constraint be journaled to the same journal when updates or deletes are performed to the parent table.
A user can stop journaling on any table using the journal functions, but doing so prevents an application from running under commitment control. If journaling is stopped on a parent table of a referential constraint with a delete rule of NO ACTION, CASCADE, SET NULL, or SET DEFAULT, all update and delete operations will be prevented. Otherwise, an application is still able to function if you have specified COMMIT(*NONE); however, this does not provide the same level of integrity that as400 journalling and commitment control provide
as400 sbmjob
as400 prtf report layout utility
AS400 PRTF - Report Layout Utility
AS400 PRTF - Report Layout Utility
The report layout utility (RLU), AS400 licensed program, allows you to create and edit source members on the AS/400 system. RLU source members in source physical
files contain the printer file Data Description Specifications (DDS) for a report
design that you lay out by using RLU.
The report design looks like an actual listing
you generate with a high-level-language (HLL) program. Using RLU, you specify
the information required for DDS, and RLU creates or changes the source member.
The DDS uses keywords to define your report design. Keywords associated with a
field are called field-level keywords, keywords associated with a record are called
record-level keywords, and keywords associated with an entire printer file are called
file-level keywords.
You can perform the following tasks with RLU:
¹ Design a report by defining it on the display, saving it as DDS source, and creating
a printer file.
¹ Create a prototype of a report by printing the as400 prtf report design at any time to make
sure that the reports looks the way you want.
as400 sbmjob
The report layout utility (RLU), AS400 licensed program, allows you to create and edit source members on the AS/400 system. RLU source members in source physical
files contain the printer file Data Description Specifications (DDS) for a report
design that you lay out by using RLU.
The report design looks like an actual listing
you generate with a high-level-language (HLL) program. Using RLU, you specify
the information required for DDS, and RLU creates or changes the source member.
The DDS uses keywords to define your report design. Keywords associated with a
field are called field-level keywords, keywords associated with a record are called
record-level keywords, and keywords associated with an entire printer file are called
file-level keywords.
You can perform the following tasks with RLU:
¹ Design a report by defining it on the display, saving it as DDS source, and creating
a printer file.
¹ Create a prototype of a report by printing the as400 prtf report design at any time to make
sure that the reports looks the way you want.
as400 sbmjob
AS400 SBMJOB examples
Examples for as400 SBMJOB
Example 1: Submitting an as400 Job
SBMJOB JOB(SPECIAL) JOBD(MYLIB/MYJOBD)
CMD(CALL MYPROG)
This as400 sbmjob command causes the job named SPECIAL to be submitted. Most of the attributes for the job are taken from the job description MYJOBD, or the job that is currently running, except for the command. The CALL command is placed on the submitted job's message queue so that the program MYPROG can be called and run later.
Example 2: Submitting an as400 Job
SBMJOB JOB(PAYROLL) JOBD(PAYROLL) INQMSGRPY(*RQD)
This as400 sbmjob command submits a job named PAYROLL to the system. All the information needed for this job (such as the job queue and routing data but not the inquiry message control value) is contained in the job description PAYROLL, or the job that is currently running. The library list in effect for the job issuing this command is used to find the job description. All inquiry messages sent during running of this job requires the receiver of the inquiry message to reply.
Example 3: Submitting an as400 Job to a Job Queue
as400 subsystem
SBMJOB JOBD(*USRPRF) JOB(COPY12) JOBQ(NIGHTQ)
CMD(CPYF FILEA FILEB)
This as400 sbmjob command submits the job COPY12, which uses the job description in the user profile of the submitting job, to the job queue NIGHTQ. The CMD parameter provides the CL command necessary for the job to run. An as400 sbmjob command such as this might be used to copy the file at night while the system is unattended.
Example 1: Submitting an as400 Job
SBMJOB JOB(SPECIAL) JOBD(MYLIB/MYJOBD)
CMD(CALL MYPROG)
This as400 sbmjob command causes the job named SPECIAL to be submitted. Most of the attributes for the job are taken from the job description MYJOBD, or the job that is currently running, except for the command. The CALL command is placed on the submitted job's message queue so that the program MYPROG can be called and run later.
Example 2: Submitting an as400 Job
SBMJOB JOB(PAYROLL) JOBD(PAYROLL) INQMSGRPY(*RQD)
This as400 sbmjob command submits a job named PAYROLL to the system. All the information needed for this job (such as the job queue and routing data but not the inquiry message control value) is contained in the job description PAYROLL, or the job that is currently running. The library list in effect for the job issuing this command is used to find the job description. All inquiry messages sent during running of this job requires the receiver of the inquiry message to reply.
Example 3: Submitting an as400 Job to a Job Queue
as400 subsystem
SBMJOB JOBD(*USRPRF) JOB(COPY12) JOBQ(NIGHTQ)
CMD(CPYF FILEA FILEB)
This as400 sbmjob command submits the job COPY12, which uses the job description in the user profile of the submitting job, to the job queue NIGHTQ. The CMD parameter provides the CL command necessary for the job to run. An as400 sbmjob command such as this might be used to copy the file at night while the system is unattended.
as400 subsystem
An as400 subsystem is described by IBM as follows
The subsystem is where work is processed on the system. A subsystem is a single, predefined operating environment through which the system coordinates the work flow and resource use. The system can contain several subsystems, all operating independently of each other. Subsystems manage resources.
All jobs, with the exception of system jobs, run within subsystems. Each subsystem can run unique operations. For instance, one subsystem may be set up to handle only interactive jobs, while another subsystem handles only batch jobs. Subsystems can also be designed to handle many types of work. The system allows you to decide the number of subsystems and what types of work each subsystem will handle.
The run-time characteristics of a subsystem are defined in an object called a subsystem description. For example, if you want to permanently change the amount of work (number of jobs) coming from a job queue into a subsystem you only need to change the job queue entry in the subsystem description.
The controlling subsystem
The controlling subsystem is the interactive subsystem that starts automatically when the system starts, and it is the subsystem through which the system operator controls the system via the system console. It is identified in the Controlling subsystem/library (QCTLSBSD) system value.
IBM® supplies two complete controlling subsystem descriptions: QBASE (the default controlling subsystem) and QCTL. Only one controlling subsystem can be active on the system at any time.
When the system is in the restricted condition, most of the activity on the system has ended, and only one workstation is active. The system must be in this condition for commands such as Save System (SAVSYS) or Reclaim Storage (RCLSTG) to run. Some programs for diagnosing equipment problems also require the system to be in a restricted condition. To end this condition, you must start the controlling subsystem again.
Note: There is also a batch restricted state in which one batch job can be active.
When all of the subsystems, including the controlling subsystem are ended, a restricted condition is created. You can end each subsystem individually or you can use the ENDSBS SBS(*ALL) OPTION(*IMMED).
Important: The system cannot reach the restricted state until there is only one job remaining in the controlling subsystem. Sometimes it may appear as though there is a single job remaining, but the system does not go into the restricted state. In this case you need to verify that there are no suspended system request jobs, suspended group jobs, or disconnected jobs on the remaining active display. Use the Work with Active Jobs (WRKACTJOB) command and press F14=Include to display any suspended or disconnected jobs. If these jobs exist, you need to end them in order for the system to reach the restricted state. The ENDSYS and ENDSBS functions will send a CPI091C information message to the command issuer when this condition is detected.
Subsystem description
A subsystem description is a system object that contains information defining the characteristics of an operating environment controlled by the system. The system-recognized identifier for the object type is *SBSD. A subsystem description defines how, where, and how much work enters a subsystem, and which resources the subsystem uses to perform the work. An active subsystem takes on the simple name of the subsystem description.
Like a set of detailed blueprints, each subsystem description is unique, containing the specific characteristics that describe the subsystem. The description includes where work can enter the subsystem, how much work the subsystem can handle, how much main storage (memory) will be used, and how quickly jobs in the subsystem can run.
You can use a subsystem description supplied with your system (with or without making changes to it), or you can create your own.
Subsystem description attributes
Subsystem description attributes are common overall system attributes. When you create a subsystem, the first step is to define the subsystem attributes.
Work entries
Work entries identify the sources where jobs can enter a subsystem. Specific types of work entries are used for different types of jobs. Work entries are part of the subsystem
description.
ibm as400 manuals v5r4
Routing entries
The routing entry identifies the main storage subsystem pool to use, the controlling program to run (typically the system-supplied program QCMD), and additional run-time information (stored in the class object). Routing entries are stored in the as400 subsystem description.
The subsystem is where work is processed on the system. A subsystem is a single, predefined operating environment through which the system coordinates the work flow and resource use. The system can contain several subsystems, all operating independently of each other. Subsystems manage resources.
All jobs, with the exception of system jobs, run within subsystems. Each subsystem can run unique operations. For instance, one subsystem may be set up to handle only interactive jobs, while another subsystem handles only batch jobs. Subsystems can also be designed to handle many types of work. The system allows you to decide the number of subsystems and what types of work each subsystem will handle.
The run-time characteristics of a subsystem are defined in an object called a subsystem description. For example, if you want to permanently change the amount of work (number of jobs) coming from a job queue into a subsystem you only need to change the job queue entry in the subsystem description.
The controlling subsystem
The controlling subsystem is the interactive subsystem that starts automatically when the system starts, and it is the subsystem through which the system operator controls the system via the system console. It is identified in the Controlling subsystem/library (QCTLSBSD) system value.
IBM® supplies two complete controlling subsystem descriptions: QBASE (the default controlling subsystem) and QCTL. Only one controlling subsystem can be active on the system at any time.
When the system is in the restricted condition, most of the activity on the system has ended, and only one workstation is active. The system must be in this condition for commands such as Save System (SAVSYS) or Reclaim Storage (RCLSTG) to run. Some programs for diagnosing equipment problems also require the system to be in a restricted condition. To end this condition, you must start the controlling subsystem again.
Note: There is also a batch restricted state in which one batch job can be active.
When all of the subsystems, including the controlling subsystem are ended, a restricted condition is created. You can end each subsystem individually or you can use the ENDSBS SBS(*ALL) OPTION(*IMMED).
Important: The system cannot reach the restricted state until there is only one job remaining in the controlling subsystem. Sometimes it may appear as though there is a single job remaining, but the system does not go into the restricted state. In this case you need to verify that there are no suspended system request jobs, suspended group jobs, or disconnected jobs on the remaining active display. Use the Work with Active Jobs (WRKACTJOB) command and press F14=Include to display any suspended or disconnected jobs. If these jobs exist, you need to end them in order for the system to reach the restricted state. The ENDSYS and ENDSBS functions will send a CPI091C information message to the command issuer when this condition is detected.
Subsystem description
A subsystem description is a system object that contains information defining the characteristics of an operating environment controlled by the system. The system-recognized identifier for the object type is *SBSD. A subsystem description defines how, where, and how much work enters a subsystem, and which resources the subsystem uses to perform the work. An active subsystem takes on the simple name of the subsystem description.
Like a set of detailed blueprints, each subsystem description is unique, containing the specific characteristics that describe the subsystem. The description includes where work can enter the subsystem, how much work the subsystem can handle, how much main storage (memory) will be used, and how quickly jobs in the subsystem can run.
You can use a subsystem description supplied with your system (with or without making changes to it), or you can create your own.
Subsystem description attributes
Subsystem description attributes are common overall system attributes. When you create a subsystem, the first step is to define the subsystem attributes.
Work entries
Work entries identify the sources where jobs can enter a subsystem. Specific types of work entries are used for different types of jobs. Work entries are part of the subsystem
description.
ibm as400 manuals v5r4
Routing entries
The routing entry identifies the main storage subsystem pool to use, the controlling program to run (typically the system-supplied program QCMD), and additional run-time information (stored in the class object). Routing entries are stored in the as400 subsystem description.
Wednesday, March 24, 2010
ibm as400 manuals v5r4
IBM AS400 Manuals v5r4.
This is a comprehensive listing of all the as400 manuals that are available as PDFS in the IBM Infocenter
8260 ATM Product Architecture
Administer iSeries Access for Windows
Advanced Functions and Administration on DB2 Universal Database for iSeries
AFP Utilities for iSeries User's Guide
Alerts Support
All You Need to Know When Migrating from IBM Firewall for AS/400
AnyMail/400 Mail Server Framework Support
APPC, APPN, and HPR
APPC Programming
Application Administration
Application Display Programming
Application programming interfaces
AS/400e Diagnostic Tools for System Administrators: An A to Z Reference for Problem Determination
AS/400 Applications: A Fast and Easy Way to Install, Set Up and Work with VRPG and CODE/400 (ADTS CS)
AS/400 Applications: Moving to the 21st Century
AS/400 APPN Configuration Guide
AS/400 Disk Storage Topics and Tools
AS/400 e-commerce: Internet Connection Servers
AS/400 Electronic-Mail Capabilities
AS/400 HTTP Server Performance and Capacity Planning
AS/400 Internet Security: Developing a Digital Certificate Infrastucture
AS/400 Internet Security: Implementing AS/400 Virtual Private Networks
AS/400 Internet Security: Protecting Your AS/400 from HARM in the Internet
AS/400 Internet Security Scenarios: A Practical Approach
AS/400 Performance Explorer Tips and Techniques
AS/400 Performance Management V3R6/V3R7
AS/400 Programming with VisualAge for RPG
AS/400 Remote Journal Function for High Availability and Data Replication
AS/400 Server Capacity Planning
AS/400 TCP/IP Autoconfiguration: DNS and DHCP Support
AS/400 XML in Action: PDML and PCML
ASCII Work Station Reference
Asynchronous Communications Programming
Asynchronous Transfer Mode (ATM) Technical Overview
ATM Configuration Examples
Availability roadmap
A Comprehensive Guide to Virtual Private Networks, Volume III: Cross-Platform Key and Policy Management
A DB2 Enterprise Query Environment -- Build It with QMF for Windows!
A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support
Backing up your system
Backup, Recovery, and Media Services (BRMS)
Backup and Recovery
Backup Recovery and Media Services for iSeries
Backup Recovery and Media Services for OS/400: A Practical Approach
Basic printing
Basic system operations
BOOTP
Bringing PHP to Your IBM eServer iSeries Server
BSC Equivalence Link Programming
Building AS/400 Applications for IBM WebSphere Standard Edition 2.0
Building AS/400 Internet-Based Applications with Java
Building iSeries Applications for WebSphere Advanced Edition 3.5
Building Java Applications for the iSeries Server with VisualAge for Java 3.5
C/C++ Legacy Class Libraries Reference
CICS Family: API Structure
CICS Family: Interproduct Communication
CICS for iSeries Administration and Operations Guide
CICS for iSeries Application Programming Guide
CICS for iSeries Intercommunication
CICS for iSeries Problem Determination
Clustering and IASPs for Higher Availability on the IBM eServer iSeries Server
Clusters
Commitment control
Common Information Model
Communications Management
Controlling system shutdown using a power-handling program
Control language (CL)
Control panel
Cryptographic Support/400
Cryptography
Customer service and support
Database administration
Database file management
Database overview
Database programming
Database troubleshooting
DB2 Multisystem
DB2 UDB/WebSphere Performance Tuning Guide
DB2 UDB for AS/400 Object Relational Support
DB2 UDB for AS/400 Visual Explain: Illustrating the Secrets
DB2 Universal Database Extenders for iSeries: Text Extender Administration and Programming
DB2 Universal Database Extenders for iSeries: XML Extender Administration and Programming Version 8
DB2 Universal Database Replication Guide and Reference Version 8
DDS concepts
DDS for display files
DDS for ICF files
DDS for physical and logical files
DDS for printer files
Developing iSeries Navigator plug-ins
DHCP
Digital Certificate Manager
Disk management
Distributed database programming
Distributed data management
Distribute software
Domain Name System
Domino for iSeries Sizing and Performance Tuning
DSNX Support
Electronic Service Agent for iSeries User's Guide
E-mail
Embedded SQL programming
Enterprise Identity Mapping (EIM)
Ethernet
Experience reports
Exploring NFS on AS/400
Extensible Markup Language
Finance Communications Programming
Frame relay
FTP
GDDM Programming Guide
GDDM Reference
Getting to know iSeries Navigator
Get started with iSeries communications
Hierarchical Storage Management
High Availability on the AS/400 System: A System Manager's Guide
i5/OS globalization
i5/OS maximum capacities
i5/OS PASE
i5/OS PASE shells and utilities
IBM 8265 Nways ATM Campus Switch
IBM AS/400 Printing V
IBM Content Manager for iSeries: Application Programming Guide and Reference
IBM Content Manager for iSeries: Getting Started with Client for Windows
IBM Content Manager for iSeries: Messages and Code
IBM Content Manager for iSeries: Planning and Installing
IBM Content Manager for iSeries: System Administration Guide
IBM Content Manager for iSeries: Understanding Advanced Workflow
IBM Content Manager OnDemand for iSeries Administration Guide
IBM Content Manager OnDemand for iSeries Common Server Administration Guide
IBM Content Manager OnDemand for iSeries Common Server Indexing Reference
IBM Content Manager OnDemand for iSeries Common Server Planning and Installation Guide
IBM Content Manager OnDemand for iSeries Common Server Web Enablement Kit Installation Guide
IBM Content Manager OnDemand for iSeries Installation Guide
IBM Content Manager OnDemand User's Guide
IBM Content Manager OnDemand Windows Client Customization Guide
IBM Developer Kit for Java
IBM Directory Server for iSeries (LDAP)
IBM eServer i5, iSeries, and AS/400e System Builder IBM i5/OS Version 5 Release 3 October 2004
IBM eServer i5 and iSeries System Handbook: IBM i5/OS Version 5 Release 3 October 2004
IBM eServer iSeries Independent ASPs: A Guide to Moving Applications to IASPs
IBM eServer iSeries Migration: System Migration and Upgrades at V5R1 and V5R2
IBM eServer iSeries Pocket Handbook: Version 5 Release 2 January 2003
IBM eServer iSeries Printing VI: Delivering the Output of e-business
IBM eServer iSeries Universal Connection for Electronic Support and Service
IBM eServer iSeries Wired Network Security: OS/400 V5R1 DCM and Cryptographic Enhancements
IBM HTTP Server (powered by Apache): An Integrated Solution for IBM eServer iSeries Servers
IBM HTTP Server for i5/OS
IBM Infoprint Fonts: Font Summary
IBM Infoprint Fonts: Introduction to Type Transformer and Utilities for Windows
IBM Infoprint Fonts: Japanese Font Library Technical Reference
IBM Infoprint Fonts: Korean Font Library Technical Reference
IBM Infoprint Fonts: Simplified Chinese Font library Technical Reference
IBM Infoprint Fonts: Traditional Chinese Font Library Technical Reference
IBM iSeries eServer IP Networks: Dynamic!
IBM Open Class Library Transition Guide
IBM Telephone Directory V5.2
IBM Toolbox for Java
IBM WebSphere Development Studio Client for iSeries Product Overview
IBM WebSphere V5.0 Security WebShpere Handbook Series
IBM Welcome Page V1.1
ICF Programming
IDDU Use
ILE Application Development Example
ILE C/C++ Compiler Reference
ILE C/C++ for AS/400 MI Library Reference
ILE C/C++ Programmer's Guide
ILE C/C++ Run-time Library Functions
ILE Concepts
Implementation and Practical Use of LDAP on the eServer iSeries Server
Infoprint Fonts: Creating User Defined Characters
Infoprint Server for iSeries: Introduction and Planning Guide
Infoprint Server for iSeries: User's Guide
Inside APPN and HPR - The Essential Guide to New SNA
Install, upgrade, or delete i5/OS and related software
Installing, upgrading, or deleting i5/OS and related software
Integrated file system
Integrating XML with DB2 XML Extender and DB2 Text Extender
Internetworking over ATM: An Introduction
Internetwork Packet Exchange (IPX) Support
Intrasystem Communications Programming
Introduction to Storage Area Networks
Intrusion detection
IP filtering and network address translation
iSeries Access for Linux
iSeries Access for Web
iSeries Access for Windows: Installation and setup
iSeries Access for Windows: Programming
iSeries Guide to Output
iSeries in Storage Area Networks: A Guide to Implementing FC Disk and Tape with iSeries
iSeries Navigator for Wireless
iSeries Navigator tasks on the Web
iSeries NetServer
iSeries Query Manager
iSeries Safety Inspection
iSeries Security Reference
iSeries Service Functions
iSeries Setting Up Your 0578, 5074, 5078 or 5079 Expansion Unit
iSeries Setting Up Your 0588, 0595, 5088, 5094, 5095, or 5294 Expansion Unit
iSeries Setting Up Your 5065 or 5066 Expansion Unit
iSeries Setting up your 5075 Expansion Unit
iSeries Setting Up Your Twinaxial System
iSeries System Debugger
Java and WebSphere Performance on IBM eServer iSeries Servers
Job Scheduler for OS/400
Journal management
LAN, Frame-Relay and ATM Support
Linux in a logical partition
Linux on an integrated xSeries solution
Linux on the IBM eServer iSeries Server: An Implementation Guide
Local Device Configuration
Logical partitions
Lotus Domino for AS/400 Internet Mail and More
Lotus Fax for Domino for AS/400: Getting the Straight Facts
Lotus Notes and Domino R5.0 Security Infrastructure Revealed
LPAR Configuration and Management Working with eServer iSeries Logical Partitions
Maintaining and managing i5/OS and related software
Management Central
Managing AS/400 V4R4 with Operations Navigator
Managing OS/400 with Operations Navigator V5R1 Volume 1: Overview and More
Managing OS/400 with Operations Navigator V5R1 Volume 2: Security
Managing OS/400 with Operations Navigator V5R1 Volume 3: Configuration and Service
Managing OS/400 with Operations Navigator V5R1 Volume 4: Packages and Products
Managing OS/400 with Operations Navigator V5R1 Volume 5: Performance Management
Managing OS/400 with Operations Navigator V5R1 Volume 6: Networking
Microsoft Windows Server 2003 Integration with iSeries
Migrating Applications from WebSphere Application Server Standard Edition to WebSphere Application Server-Express V5
Migration
Migration Options for OS/2 Warp Server for AS/400 and OS/400 Integration for Novell NetWare
MQSeries Backup and Recovery
MQSeries Version 5 Programming Examples
Multithreaded applications
NetWare on iSeries
Networking scenarios
Network authentication service
New Enterprise Integration Functions for Lotus Domino for AS/400
Object signing and signature verification
Operations Console
Optical device programming
OptiConnect
OS/400 Maximum Capacities - V5R2
OS/400 Network File System Support
OS/400 V5R2 Virtual Private Networks: Remote Access to the IBM eServer iSeries Server with Windows 2000 VPN Clients
Performance
Performance and query optimization
Performance Tools for iSeries
Planning a backup and recovery strategy
Plan for and set up system security
Programming with VisualAge RPG
Qshell
Quality of service
Query for iSeries Use
Query Management Facility for Windows
Query Management Programming
Query Manager Use
Rack Lift Tool Operation and Maintenance
Remote Access Services: PPP connections
Remote Work Station Support
REXEC
REXX/400 Programmer's Guide
REXX/400 Reference
Roadmap to Availability on the iSeries 400
RouteD
Secure Sockets Layer (SSL)
Service tools user IDs and passwords
Simple Network Management Protocol (SNMP) Support
Simple Network Time Protocol (SNTP)
Single signon
Slicing the AS/400 with Logical Partitioning: A How to Guide
SNA and TCP/IP Integration
SNA Upline Facility Programming
Socket programming
Speak the Right Language with Your AS/400 System
Spooled files
SQL Call Level Interface (ODBC)
SQL messages and codes
SQL programming
SQL reference
Standard C++ Library Reference
Storage solutions
Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries
Striving for Optimal Journal Performance on DB2 Universal Database for iSeries
Subarea to APPN Migration: HPR and DLUR Implementation
System/36-AS/400 Command Cross-Reference
System/36 Environment Programming
System/36 Environment Reference
System/38 Environment Programming
System i and Internet security
System i Performance Capabilities Reference
System Manager Use
System values
Tape files
TCP/IP routing and workload balancing
TCP/IP setup
TCP/IP troubleshooting
TCP/IP Tutorial and Technical Overview
Telnet
TFTP
The AS/400 NetServer Advantage
The IBM Total Storage Tape Libraries Guide for Open Systems
The System Administrator's Companion to AS/400 Availability and Recovery
The XML Files: Using XML for Business-to-Business and Business-to-Consumer Applications
Time management
Token ring
Troubleshooting
Troubleshooting IBM LAN/ATM Campus Networks
Understanding LDAP -- Design and Implementation
Universal Connection
Unleashing AS/400 Applications on the Internet
Upgrades
Using AS/400 Database Monitor and Visual Explain to Identify and Tune SQL Queries
Using LDAP for Directory Integration
Using Sun TI-RPC to develop distributed applications
Using the AS/400 as an IPX Router
V4 TCP/IP for AS/400: More Cool Things Than Ever
Virtual private networking (VPN)
VisualAge RPG Language Reference
VisualAge RPG Parts Reference
WebSphere Application Server for i5/OS V6
WebSphere Application Server for iSeries
WebSphere Application Server - Express for iSeries V5
WebSphere Application Server - Express for iSeries V5.1
WebSphere Application Server - Express V5.0.2 Developer Handbook
WebSphere Application Server - Express V5.0 for iSeries
WebSphere Development Studio: ILE C/C++ Language Reference
WebSphere Development Studio: ILE COBOL Programmer's Guide
WebSphere Development Studio: ILE COBOL Reference
WebSphere Development Studio: ILE RPG Programmer's Guide
WebSphere Development Studio: ILE RPG Reference
WebSphere J2EE Application Development for the IBM eServer iSeries Server
Websphere V3 Performance Tuning Guide
Web Enabling AS/400 Applications with IBM WebSphere Studio
Who Knew You Could Do That with RPG IV? A Sorcerer's Guide to System Access and More
Windows environment on iSeries
Workstation Customization Programming
Work management
X.25 Network Support
XML Toolkit for iSeries
as400 query
This is a comprehensive listing of all the as400 manuals that are available as PDFS in the IBM Infocenter
8260 ATM Product Architecture
Administer iSeries Access for Windows
Advanced Functions and Administration on DB2 Universal Database for iSeries
AFP Utilities for iSeries User's Guide
Alerts Support
All You Need to Know When Migrating from IBM Firewall for AS/400
AnyMail/400 Mail Server Framework Support
APPC, APPN, and HPR
APPC Programming
Application Administration
Application Display Programming
Application programming interfaces
AS/400e Diagnostic Tools for System Administrators: An A to Z Reference for Problem Determination
AS/400 Applications: A Fast and Easy Way to Install, Set Up and Work with VRPG and CODE/400 (ADTS CS)
AS/400 Applications: Moving to the 21st Century
AS/400 APPN Configuration Guide
AS/400 Disk Storage Topics and Tools
AS/400 e-commerce: Internet Connection Servers
AS/400 Electronic-Mail Capabilities
AS/400 HTTP Server Performance and Capacity Planning
AS/400 Internet Security: Developing a Digital Certificate Infrastucture
AS/400 Internet Security: Implementing AS/400 Virtual Private Networks
AS/400 Internet Security: Protecting Your AS/400 from HARM in the Internet
AS/400 Internet Security Scenarios: A Practical Approach
AS/400 Performance Explorer Tips and Techniques
AS/400 Performance Management V3R6/V3R7
AS/400 Programming with VisualAge for RPG
AS/400 Remote Journal Function for High Availability and Data Replication
AS/400 Server Capacity Planning
AS/400 TCP/IP Autoconfiguration: DNS and DHCP Support
AS/400 XML in Action: PDML and PCML
ASCII Work Station Reference
Asynchronous Communications Programming
Asynchronous Transfer Mode (ATM) Technical Overview
ATM Configuration Examples
Availability roadmap
A Comprehensive Guide to Virtual Private Networks, Volume III: Cross-Platform Key and Policy Management
A DB2 Enterprise Query Environment -- Build It with QMF for Windows!
A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support
Backing up your system
Backup, Recovery, and Media Services (BRMS)
Backup and Recovery
Backup Recovery and Media Services for iSeries
Backup Recovery and Media Services for OS/400: A Practical Approach
Basic printing
Basic system operations
BOOTP
Bringing PHP to Your IBM eServer iSeries Server
BSC Equivalence Link Programming
Building AS/400 Applications for IBM WebSphere Standard Edition 2.0
Building AS/400 Internet-Based Applications with Java
Building iSeries Applications for WebSphere Advanced Edition 3.5
Building Java Applications for the iSeries Server with VisualAge for Java 3.5
C/C++ Legacy Class Libraries Reference
CICS Family: API Structure
CICS Family: Interproduct Communication
CICS for iSeries Administration and Operations Guide
CICS for iSeries Application Programming Guide
CICS for iSeries Intercommunication
CICS for iSeries Problem Determination
Clustering and IASPs for Higher Availability on the IBM eServer iSeries Server
Clusters
Commitment control
Common Information Model
Communications Management
Controlling system shutdown using a power-handling program
Control language (CL)
Control panel
Cryptographic Support/400
Cryptography
Customer service and support
Database administration
Database file management
Database overview
Database programming
Database troubleshooting
DB2 Multisystem
DB2 UDB/WebSphere Performance Tuning Guide
DB2 UDB for AS/400 Object Relational Support
DB2 UDB for AS/400 Visual Explain: Illustrating the Secrets
DB2 Universal Database Extenders for iSeries: Text Extender Administration and Programming
DB2 Universal Database Extenders for iSeries: XML Extender Administration and Programming Version 8
DB2 Universal Database Replication Guide and Reference Version 8
DDS concepts
DDS for display files
DDS for ICF files
DDS for physical and logical files
DDS for printer files
Developing iSeries Navigator plug-ins
DHCP
Digital Certificate Manager
Disk management
Distributed database programming
Distributed data management
Distribute software
Domain Name System
Domino for iSeries Sizing and Performance Tuning
DSNX Support
Electronic Service Agent for iSeries User's Guide
Embedded SQL programming
Enterprise Identity Mapping (EIM)
Ethernet
Experience reports
Exploring NFS on AS/400
Extensible Markup Language
Finance Communications Programming
Frame relay
FTP
GDDM Programming Guide
GDDM Reference
Getting to know iSeries Navigator
Get started with iSeries communications
Hierarchical Storage Management
High Availability on the AS/400 System: A System Manager's Guide
i5/OS globalization
i5/OS maximum capacities
i5/OS PASE
i5/OS PASE shells and utilities
IBM 8265 Nways ATM Campus Switch
IBM AS/400 Printing V
IBM Content Manager for iSeries: Application Programming Guide and Reference
IBM Content Manager for iSeries: Getting Started with Client for Windows
IBM Content Manager for iSeries: Messages and Code
IBM Content Manager for iSeries: Planning and Installing
IBM Content Manager for iSeries: System Administration Guide
IBM Content Manager for iSeries: Understanding Advanced Workflow
IBM Content Manager OnDemand for iSeries Administration Guide
IBM Content Manager OnDemand for iSeries Common Server Administration Guide
IBM Content Manager OnDemand for iSeries Common Server Indexing Reference
IBM Content Manager OnDemand for iSeries Common Server Planning and Installation Guide
IBM Content Manager OnDemand for iSeries Common Server Web Enablement Kit Installation Guide
IBM Content Manager OnDemand for iSeries Installation Guide
IBM Content Manager OnDemand User's Guide
IBM Content Manager OnDemand Windows Client Customization Guide
IBM Developer Kit for Java
IBM Directory Server for iSeries (LDAP)
IBM eServer i5, iSeries, and AS/400e System Builder IBM i5/OS Version 5 Release 3 October 2004
IBM eServer i5 and iSeries System Handbook: IBM i5/OS Version 5 Release 3 October 2004
IBM eServer iSeries Independent ASPs: A Guide to Moving Applications to IASPs
IBM eServer iSeries Migration: System Migration and Upgrades at V5R1 and V5R2
IBM eServer iSeries Pocket Handbook: Version 5 Release 2 January 2003
IBM eServer iSeries Printing VI: Delivering the Output of e-business
IBM eServer iSeries Universal Connection for Electronic Support and Service
IBM eServer iSeries Wired Network Security: OS/400 V5R1 DCM and Cryptographic Enhancements
IBM HTTP Server (powered by Apache): An Integrated Solution for IBM eServer iSeries Servers
IBM HTTP Server for i5/OS
IBM Infoprint Fonts: Font Summary
IBM Infoprint Fonts: Introduction to Type Transformer and Utilities for Windows
IBM Infoprint Fonts: Japanese Font Library Technical Reference
IBM Infoprint Fonts: Korean Font Library Technical Reference
IBM Infoprint Fonts: Simplified Chinese Font library Technical Reference
IBM Infoprint Fonts: Traditional Chinese Font Library Technical Reference
IBM iSeries eServer IP Networks: Dynamic!
IBM Open Class Library Transition Guide
IBM Telephone Directory V5.2
IBM Toolbox for Java
IBM WebSphere Development Studio Client for iSeries Product Overview
IBM WebSphere V5.0 Security WebShpere Handbook Series
IBM Welcome Page V1.1
ICF Programming
IDDU Use
ILE Application Development Example
ILE C/C++ Compiler Reference
ILE C/C++ for AS/400 MI Library Reference
ILE C/C++ Programmer's Guide
ILE C/C++ Run-time Library Functions
ILE Concepts
Implementation and Practical Use of LDAP on the eServer iSeries Server
Infoprint Fonts: Creating User Defined Characters
Infoprint Server for iSeries: Introduction and Planning Guide
Infoprint Server for iSeries: User's Guide
Inside APPN and HPR - The Essential Guide to New SNA
Install, upgrade, or delete i5/OS and related software
Installing, upgrading, or deleting i5/OS and related software
Integrated file system
Integrating XML with DB2 XML Extender and DB2 Text Extender
Internetworking over ATM: An Introduction
Internetwork Packet Exchange (IPX) Support
Intrasystem Communications Programming
Introduction to Storage Area Networks
Intrusion detection
IP filtering and network address translation
iSeries Access for Linux
iSeries Access for Web
iSeries Access for Windows: Installation and setup
iSeries Access for Windows: Programming
iSeries Guide to Output
iSeries in Storage Area Networks: A Guide to Implementing FC Disk and Tape with iSeries
iSeries Navigator for Wireless
iSeries Navigator tasks on the Web
iSeries NetServer
iSeries Query Manager
iSeries Safety Inspection
iSeries Security Reference
iSeries Service Functions
iSeries Setting Up Your 0578, 5074, 5078 or 5079 Expansion Unit
iSeries Setting Up Your 0588, 0595, 5088, 5094, 5095, or 5294 Expansion Unit
iSeries Setting Up Your 5065 or 5066 Expansion Unit
iSeries Setting up your 5075 Expansion Unit
iSeries Setting Up Your Twinaxial System
iSeries System Debugger
Java and WebSphere Performance on IBM eServer iSeries Servers
Job Scheduler for OS/400
Journal management
LAN, Frame-Relay and ATM Support
Linux in a logical partition
Linux on an integrated xSeries solution
Linux on the IBM eServer iSeries Server: An Implementation Guide
Local Device Configuration
Logical partitions
Lotus Domino for AS/400 Internet Mail and More
Lotus Fax for Domino for AS/400: Getting the Straight Facts
Lotus Notes and Domino R5.0 Security Infrastructure Revealed
LPAR Configuration and Management Working with eServer iSeries Logical Partitions
Maintaining and managing i5/OS and related software
Management Central
Managing AS/400 V4R4 with Operations Navigator
Managing OS/400 with Operations Navigator V5R1 Volume 1: Overview and More
Managing OS/400 with Operations Navigator V5R1 Volume 2: Security
Managing OS/400 with Operations Navigator V5R1 Volume 3: Configuration and Service
Managing OS/400 with Operations Navigator V5R1 Volume 4: Packages and Products
Managing OS/400 with Operations Navigator V5R1 Volume 5: Performance Management
Managing OS/400 with Operations Navigator V5R1 Volume 6: Networking
Microsoft Windows Server 2003 Integration with iSeries
Migrating Applications from WebSphere Application Server Standard Edition to WebSphere Application Server-Express V5
Migration
Migration Options for OS/2 Warp Server for AS/400 and OS/400 Integration for Novell NetWare
MQSeries Backup and Recovery
MQSeries Version 5 Programming Examples
Multithreaded applications
NetWare on iSeries
Networking scenarios
Network authentication service
New Enterprise Integration Functions for Lotus Domino for AS/400
Object signing and signature verification
Operations Console
Optical device programming
OptiConnect
OS/400 Maximum Capacities - V5R2
OS/400 Network File System Support
OS/400 V5R2 Virtual Private Networks: Remote Access to the IBM eServer iSeries Server with Windows 2000 VPN Clients
Performance
Performance and query optimization
Performance Tools for iSeries
Planning a backup and recovery strategy
Plan for and set up system security
Programming with VisualAge RPG
Qshell
Quality of service
Query for iSeries Use
Query Management Facility for Windows
Query Management Programming
Query Manager Use
Rack Lift Tool Operation and Maintenance
Remote Access Services: PPP connections
Remote Work Station Support
REXEC
REXX/400 Programmer's Guide
REXX/400 Reference
Roadmap to Availability on the iSeries 400
RouteD
Secure Sockets Layer (SSL)
Service tools user IDs and passwords
Simple Network Management Protocol (SNMP) Support
Simple Network Time Protocol (SNTP)
Single signon
Slicing the AS/400 with Logical Partitioning: A How to Guide
SNA and TCP/IP Integration
SNA Upline Facility Programming
Socket programming
Speak the Right Language with Your AS/400 System
Spooled files
SQL Call Level Interface (ODBC)
SQL messages and codes
SQL programming
SQL reference
Standard C++ Library Reference
Storage solutions
Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries
Striving for Optimal Journal Performance on DB2 Universal Database for iSeries
Subarea to APPN Migration: HPR and DLUR Implementation
System/36-AS/400 Command Cross-Reference
System/36 Environment Programming
System/36 Environment Reference
System/38 Environment Programming
System i and Internet security
System i Performance Capabilities Reference
System Manager Use
System values
Tape files
TCP/IP routing and workload balancing
TCP/IP setup
TCP/IP troubleshooting
TCP/IP Tutorial and Technical Overview
Telnet
TFTP
The AS/400 NetServer Advantage
The IBM Total Storage Tape Libraries Guide for Open Systems
The System Administrator's Companion to AS/400 Availability and Recovery
The XML Files: Using XML for Business-to-Business and Business-to-Consumer Applications
Time management
Token ring
Troubleshooting
Troubleshooting IBM LAN/ATM Campus Networks
Understanding LDAP -- Design and Implementation
Universal Connection
Unleashing AS/400 Applications on the Internet
Upgrades
Using AS/400 Database Monitor and Visual Explain to Identify and Tune SQL Queries
Using LDAP for Directory Integration
Using Sun TI-RPC to develop distributed applications
Using the AS/400 as an IPX Router
V4 TCP/IP for AS/400: More Cool Things Than Ever
Virtual private networking (VPN)
VisualAge RPG Language Reference
VisualAge RPG Parts Reference
WebSphere Application Server for i5/OS V6
WebSphere Application Server for iSeries
WebSphere Application Server - Express for iSeries V5
WebSphere Application Server - Express for iSeries V5.1
WebSphere Application Server - Express V5.0.2 Developer Handbook
WebSphere Application Server - Express V5.0 for iSeries
WebSphere Development Studio: ILE C/C++ Language Reference
WebSphere Development Studio: ILE COBOL Programmer's Guide
WebSphere Development Studio: ILE COBOL Reference
WebSphere Development Studio: ILE RPG Programmer's Guide
WebSphere Development Studio: ILE RPG Reference
WebSphere J2EE Application Development for the IBM eServer iSeries Server
Websphere V3 Performance Tuning Guide
Web Enabling AS/400 Applications with IBM WebSphere Studio
Who Knew You Could Do That with RPG IV? A Sorcerer's Guide to System Access and More
Windows environment on iSeries
Workstation Customization Programming
Work management
X.25 Network Support
XML Toolkit for iSeries
as400 query
Sunday, March 21, 2010
Creating an as400 Query
Creating an as400 Query
As400 Query, QUERY/400 as it used to be called or IBM Query for Iseries, which is whats shown these days on the Installed Licensed Programs screen (GO LICPGM Option 10) is probably one of the most widely used tools on the as400. This tool has a very straightforward user interface, so is often used by both technical and non technical staff (with relevant training of course). Its a very easy to use tool for displaying, extracting and reporting on data. Query is a separate chargeable product, but I don’t think I have ever worked at a site that didn’t have it installed on all their systems, so you can be pretty sure almost every AS/400 will have the IBM provided query language QUERY/400.
As an example, we will write a query over a typical database file that almost every business system has, the CUSTOMER master file. After extracting the data we will create a report.
At the as400 command line type in WRKQRY and hit ENTER:
----------------------------------------------------------------
Work with Queries
Type choices, press Enter.
Option . . . . . . 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . Name, F4 for list
Library . . . . . QGPL Name, *LIBL, F4 for list
-----------------------------------------------------------------
We will create a query to view the data in the CUSTOMER master file. Type in 1 for the option to create a query and the press ENTER.
-----------------------------------------------------------------
Define the Query
Query . . . . . . : Option . . . . . : CREATE
Library . . . . : QGPL CCSID . . . . . . : 65535
Type options, press Enter. Press F21 to select all.
1=Select
Opt Query Definition Option
1 Specify file selections
Define result fields
Select and sequence fields
Select records
Select sort fields
Select collating sequence
Specify report column formatting
Select report summary functions
Define report breaks
Select output type and output form
Specify processing options
-----------------------------------------------------------------
Next "Specify file selections". This is already selected by default so just hit ENTER and you will see:
--------------------------------------------------------------
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File . . . . . . . . . Name, F4 for list
Library . . . . . . QGPL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list
---------------------------------------------------------------
Enter CUSTOMER (or whatever your customer master file is called) as the file you want to query and YOURLIB (replace with your relevant database library) as the Library and press ENTER. Pressing ENTER again will take you back to the Define the Query screen.
The next option down, the Define result fields option, is described thus: “Select this option either to define one or more new fields to
be used as result fields or to see what result fields are already defined for the query. The new fields will hold the results of arithmetic, character, or date and time operations done on other fields in this query”. In this case we don’t need any result fields so we can ignore this option for now.
We do need to select fields that we want to report on. Type 1 next to "Select and Sequence Fields" and press ENTER
You should now see:
----------------------------------------------------------------
Type sequence number (0-9999) for the names of up to 500 fields to
appear in the report, press Enter.
Seq Field Text Len Dec
CUSTN Customer Number 10 0
CUSTNA1 Cust. Name & Address 1 50
CUSTNA2 Cust. Name & Address 2 50
CUSTNA3 Cust. Name & Address 3 50
CUSTNA4 Cust. Name & Address 4 50
CUSTTOWN Customer Town 10
CUSTREPN Customer Report Name 20
LOCCD Location Code 5
More...
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
----------------------------------------------------------------
Since the AS/400 Query knows the file you want to see, as you selected it earlier, it has retrieved the fields in the file and listed them for you to choose from.
Weare going to select the following columns:
cust number
cust name and address 1
cust town
location code
We need to enter a number to select each of the fields that we require, so enter a number next to each field, which will determine the order in which they are displayed. Enter 2 next to CUSTNA1, 1 next to CUSTN, 4 next to CUSTTOWN, and 6 next to LOCCD. Press ENTER twice and then you will be back at the definition screen.
as400 QUERY allows you to view the results of the query while you are modifying it, which is handy as it allows you correct any errors there and then
---------------------------------------------------------------
Display Report
Report width . . . . . : 115
Position to line . . . . . Shift to column . . .
....1....+....2....+....3....+....4....+....5....+....6....+....7
Customer Name and Address 1 Town Location
Number Code
000001 000001 Davis, 2 High St London 010
000002 000012 Charles, 1 High St London 010
000003 000015 Fish, 3 High St London 010
000004 000020 Brompton, 4 High St London 010
-----------------------------------------------------------------
This is the first steps to creating a query that shows records on an as400 database file.
The standard IBM CUA window keys are available on this screen. F20 will move the data window to the right, F19 will move the data window back to the left. F21 will split the screen into two mini windows, one fixed and one moveable, so you can view 2 parts of the data similultaneously. F12 to cancel returns to the main definition screen
Next is to take option 1 for Column Formatting to modify the column headings to be more descriptive. This allows you to change them to be more meaningful to the out being produced.
To change the sequencing of the query, type 1 next to "Select Sort Fields". Typing a 1 next to the customer number field will ensure that the data is produced in customer number order. Pressing F5 will allow review of the result of applying the sort to the customer number field.
The next step is to decide which records to select. Enter a 1 next to select records. In this case we only want to select records with a location code of '030'. The screen will look like:
----------------------------------------------------------
Select Records
Type comparisons, press Enter. Specify OR to start each new
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISN
AND/OR Field Test Value (Field, Number, 'Char
LOCCD EQ '030'
----------------------------------------------------------
Now the extract will only show customers with a location code of '030'.
Now we need to save the query. Pressing F3 and filling in detals give:
-------------------------------------------------------------
Exit this Query
Type choices, press Enter.
Save definition . . . Y Y=Yes, N=No
Run option . . . . . . 3 1=Run interactively
2=Run in batch
3=Do not run
For a saved definition:
Query . . . . . . . TEMPQRY Name
Library . . . . . QGPL Name, F4 for list
Text . . . . . . . . Display CUSTOMER Master File
---------------------------------------------------------------
And to run the query, from an AS400 command line, key in the command to run a query:
RUNQRY QGPL,TEMPQRY
This should show the results for the as400 query.
As400 Query, QUERY/400 as it used to be called or IBM Query for Iseries, which is whats shown these days on the Installed Licensed Programs screen (GO LICPGM Option 10) is probably one of the most widely used tools on the as400. This tool has a very straightforward user interface, so is often used by both technical and non technical staff (with relevant training of course). Its a very easy to use tool for displaying, extracting and reporting on data. Query is a separate chargeable product, but I don’t think I have ever worked at a site that didn’t have it installed on all their systems, so you can be pretty sure almost every AS/400 will have the IBM provided query language QUERY/400.
As an example, we will write a query over a typical database file that almost every business system has, the CUSTOMER master file. After extracting the data we will create a report.
At the as400 command line type in WRKQRY and hit ENTER:
----------------------------------------------------------------
Work with Queries
Type choices, press Enter.
Option . . . . . . 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . Name, F4 for list
Library . . . . . QGPL Name, *LIBL, F4 for list
-----------------------------------------------------------------
We will create a query to view the data in the CUSTOMER master file. Type in 1 for the option to create a query and the press ENTER.
-----------------------------------------------------------------
Define the Query
Query . . . . . . : Option . . . . . : CREATE
Library . . . . : QGPL CCSID . . . . . . : 65535
Type options, press Enter. Press F21 to select all.
1=Select
Opt Query Definition Option
1 Specify file selections
Define result fields
Select and sequence fields
Select records
Select sort fields
Select collating sequence
Specify report column formatting
Select report summary functions
Define report breaks
Select output type and output form
Specify processing options
-----------------------------------------------------------------
Next "Specify file selections". This is already selected by default so just hit ENTER and you will see:
--------------------------------------------------------------
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File . . . . . . . . . Name, F4 for list
Library . . . . . . QGPL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list
---------------------------------------------------------------
Enter CUSTOMER (or whatever your customer master file is called) as the file you want to query and YOURLIB (replace with your relevant database library) as the Library and press ENTER. Pressing ENTER again will take you back to the Define the Query screen.
The next option down, the Define result fields option, is described thus: “Select this option either to define one or more new fields to
be used as result fields or to see what result fields are already defined for the query. The new fields will hold the results of arithmetic, character, or date and time operations done on other fields in this query”. In this case we don’t need any result fields so we can ignore this option for now.
We do need to select fields that we want to report on. Type 1 next to "Select and Sequence Fields" and press ENTER
You should now see:
----------------------------------------------------------------
Type sequence number (0-9999) for the names of up to 500 fields to
appear in the report, press Enter.
Seq Field Text Len Dec
CUSTN Customer Number 10 0
CUSTNA1 Cust. Name & Address 1 50
CUSTNA2 Cust. Name & Address 2 50
CUSTNA3 Cust. Name & Address 3 50
CUSTNA4 Cust. Name & Address 4 50
CUSTTOWN Customer Town 10
CUSTREPN Customer Report Name 20
LOCCD Location Code 5
More...
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
----------------------------------------------------------------
Since the AS/400 Query knows the file you want to see, as you selected it earlier, it has retrieved the fields in the file and listed them for you to choose from.
Weare going to select the following columns:
cust number
cust name and address 1
cust town
location code
We need to enter a number to select each of the fields that we require, so enter a number next to each field, which will determine the order in which they are displayed. Enter 2 next to CUSTNA1, 1 next to CUSTN, 4 next to CUSTTOWN, and 6 next to LOCCD. Press ENTER twice and then you will be back at the definition screen.
as400 QUERY allows you to view the results of the query while you are modifying it, which is handy as it allows you correct any errors there and then
---------------------------------------------------------------
Display Report
Report width . . . . . : 115
Position to line . . . . . Shift to column . . .
....1....+....2....+....3....+....4....+....5....+....6....+....7
Customer Name and Address 1 Town Location
Number Code
000001 000001 Davis, 2 High St London 010
000002 000012 Charles, 1 High St London 010
000003 000015 Fish, 3 High St London 010
000004 000020 Brompton, 4 High St London 010
-----------------------------------------------------------------
This is the first steps to creating a query that shows records on an as400 database file.
The standard IBM CUA window keys are available on this screen. F20 will move the data window to the right, F19 will move the data window back to the left. F21 will split the screen into two mini windows, one fixed and one moveable, so you can view 2 parts of the data similultaneously. F12 to cancel returns to the main definition screen
Next is to take option 1 for Column Formatting to modify the column headings to be more descriptive. This allows you to change them to be more meaningful to the out being produced.
To change the sequencing of the query, type 1 next to "Select Sort Fields". Typing a 1 next to the customer number field will ensure that the data is produced in customer number order. Pressing F5 will allow review of the result of applying the sort to the customer number field.
The next step is to decide which records to select. Enter a 1 next to select records. In this case we only want to select records with a location code of '030'. The screen will look like:
----------------------------------------------------------
Select Records
Type comparisons, press Enter. Specify OR to start each new
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISN
AND/OR Field Test Value (Field, Number, 'Char
LOCCD EQ '030'
----------------------------------------------------------
Now the extract will only show customers with a location code of '030'.
Now we need to save the query. Pressing F3 and filling in detals give:
-------------------------------------------------------------
Exit this Query
Type choices, press Enter.
Save definition . . . Y Y=Yes, N=No
Run option . . . . . . 3 1=Run interactively
2=Run in batch
3=Do not run
For a saved definition:
Query . . . . . . . TEMPQRY Name
Library . . . . . QGPL Name, F4 for list
Text . . . . . . . . Display CUSTOMER Master File
---------------------------------------------------------------
And to run the query, from an AS400 command line, key in the command to run a query:
RUNQRY QGPL,TEMPQRY
This should show the results for the as400 query.
as400 virtual terminals
A virtual terminal is a device that does not have hardware associated with it. It forms a connection between your application and AS/400 applications, representing a physical work station (possibly on a remote system). The OS/400 licensed program manages the virtual terminal, which directs work station I/O performed by an AS/400 application to the virtual terminal. The virtual terminal APIs allow another AS/400 application, called a server program, to work with the data associated with the virtual terminal.
In a distributed systems environment, the requesting program is called a client; the answering program is called a server. The client and server programs may reside on the same AS/400 system or may be distributed between two different systems. The server program generally runs on behalf of (or in conjunction with) the client program. Together, the server program and the client program allow a work station to be supported as if the work station were connected locally, but is in fact a virtual terminal.
In a distributed systems environment, the requesting program is called a client; the answering program is called a server. The client and server programs may reside on the same AS/400 system or may be distributed between two different systems. The server program generally runs on behalf of (or in conjunction with) the client program. Together, the server program and the client program allow a work station to be supported as if the work station were connected locally, but is in fact a virtual terminal.
Friday, March 19, 2010
as400 jobs - Lead DB2 DBA(Z/OS or AIX or LUW or OS/400) - IP Soft - Bangalore, KA
DB2 DBA for IPSoft, one of the fastest growing MSPs in the US. • Schema Design Review, Physical Tablespace Layout• DB2 Server Installation, Database Creation, Alteration, and Setup• DB2 Server Database File Management.Nice to have a IBM Certification
Sunday, March 14, 2010
Differences between iseries as400 API QMHLJOBL and QGYOLJBL
Differences between iseries as400 QMHLJOBL and QGYOLJBL
The Open List of Job Log Messages (QGYOLJBL) API lists messages from a job log. The returned messages are sorted by their sending date and time unless the message being listed is a reply message to an inquiry, a sender's copy, or a notify type message. When a reply message exists for one of these messages, the reply message is not positioned according to the sending date and time of the reply. The reply message is, instead, listed immediately following the inquiry, the sender's copy, or the notify message that it is associated with
The List Job Log Messages (QMHLJOBL) API lists messages sent to the job message queue of a job. This API gets the requested message information and returns it in a user space in the format specified in the parameter list. The following discusses how the list is sorted for nonbatch jobs and for batch jobs.
When the job whose messages are being listed is not a batch job, the returned messages are sorted by their sending date and time unless the message being listed is a reply message to an inquiry, a sender's copy, or a notify message. If it is a reply message, it is listed immediately following the inquiry, sender's copy, or notify message with which it is associated.
If the last message listed is an inquiry, a sender's copy, or a notify message, the user of the API must call the API again using parameters that would include listing the next later message after the inquiry, sender's copy, or notify message in order to obtain an available reply message.
When the job whose messages are listed is a batch job, the messages are grouped into two categories:
Request messages that have been or are being processed, and the other messages that occurred during the processing of those requests.
Request messages that are yet to be processed, and any diagnostic messages associated with these request messages.
The API treats unprocessed request messages as if they had a sending time later than all the request messages and their associated messages that have been or are being processed. The following two examples describe the sorting further.
For example, if the call to this API specifies to list the messages for a batch job from oldest to newest, the list consists of all requests and their associated messages that have been or are being processed. They are sorted as described above for a job that is not a batch job. They are followed by any request messages and any associated diagnostic messages that have not yet been processed (in the order that they will be processed).
As an opposite example, if the call to this API specifies to list the messages for a batch job from newest to oldest, the list consists of the request messages that remain to be processed. They are in the opposite order that they are processed. They are followed by the request messages that have been or are being processed and their associated messages. These are sorted backward through time as described above for nonbatch jobs.
The generated list replaces any existing information in the user space.
If the user space is not large enough to contain the data to be returned, the user space is increased to the maximum user space size allowed (16MB) or the maximum amount of storage allowed to the user of the API. If this is not large enough to contain the data to be returned, only the number of complete messages that fit in the user space are returned. The information status field in the generic header is set to P (partial but accurate). The user can then resubmit the request from the last message returned to obtain the additional messages. The key of the last message listed for each message queue is provided in the ending message key field in the header portion of the user space.
The maximum messages requested field and the number of fields to return field for each listed message increase the system resources required to create the list. Users of this API should use caution when specifying parameters that list many messages or request many identified fields to be returned for each listed message
The differences between the QGYOLJBL API and the List Job Log Messages (QMHLJOBL) API follow:
The message information for the QGYOLJBL API is returned into a receiver variable instead of a user space.
The request handle parameter was added to the QGYOLJBL API, so that a distinct value is associated with the list. This value ensures that a request for more messages from the list (call QGYGTLE API) is sent to the correct list.
The total records parameter was added to the QGYOLJBL API, so that the caller of the API knows the size of the list from which information can be requested.
The records returned parameter was added to the QGYOLJBL API. Now if the request is for X records and only Y records exist (where Y < X), the requestor is aware that it did not get the number of records requested.
The amount of information returned by the QMHLJOBL API is limited by the size of a user space, approximately 16MB. The list created by the QGYOLJBL API can contain up to 2GB of information
The Open List of Job Log Messages (QGYOLJBL) API lists messages from a job log. The returned messages are sorted by their sending date and time unless the message being listed is a reply message to an inquiry, a sender's copy, or a notify type message. When a reply message exists for one of these messages, the reply message is not positioned according to the sending date and time of the reply. The reply message is, instead, listed immediately following the inquiry, the sender's copy, or the notify message that it is associated with
The List Job Log Messages (QMHLJOBL) API lists messages sent to the job message queue of a job. This API gets the requested message information and returns it in a user space in the format specified in the parameter list. The following discusses how the list is sorted for nonbatch jobs and for batch jobs.
When the job whose messages are being listed is not a batch job, the returned messages are sorted by their sending date and time unless the message being listed is a reply message to an inquiry, a sender's copy, or a notify message. If it is a reply message, it is listed immediately following the inquiry, sender's copy, or notify message with which it is associated.
If the last message listed is an inquiry, a sender's copy, or a notify message, the user of the API must call the API again using parameters that would include listing the next later message after the inquiry, sender's copy, or notify message in order to obtain an available reply message.
When the job whose messages are listed is a batch job, the messages are grouped into two categories:
Request messages that have been or are being processed, and the other messages that occurred during the processing of those requests.
Request messages that are yet to be processed, and any diagnostic messages associated with these request messages.
The API treats unprocessed request messages as if they had a sending time later than all the request messages and their associated messages that have been or are being processed. The following two examples describe the sorting further.
For example, if the call to this API specifies to list the messages for a batch job from oldest to newest, the list consists of all requests and their associated messages that have been or are being processed. They are sorted as described above for a job that is not a batch job. They are followed by any request messages and any associated diagnostic messages that have not yet been processed (in the order that they will be processed).
As an opposite example, if the call to this API specifies to list the messages for a batch job from newest to oldest, the list consists of the request messages that remain to be processed. They are in the opposite order that they are processed. They are followed by the request messages that have been or are being processed and their associated messages. These are sorted backward through time as described above for nonbatch jobs.
The generated list replaces any existing information in the user space.
If the user space is not large enough to contain the data to be returned, the user space is increased to the maximum user space size allowed (16MB) or the maximum amount of storage allowed to the user of the API. If this is not large enough to contain the data to be returned, only the number of complete messages that fit in the user space are returned. The information status field in the generic header is set to P (partial but accurate). The user can then resubmit the request from the last message returned to obtain the additional messages. The key of the last message listed for each message queue is provided in the ending message key field in the header portion of the user space.
The maximum messages requested field and the number of fields to return field for each listed message increase the system resources required to create the list. Users of this API should use caution when specifying parameters that list many messages or request many identified fields to be returned for each listed message
The differences between the QGYOLJBL API and the List Job Log Messages (QMHLJOBL) API follow:
The message information for the QGYOLJBL API is returned into a receiver variable instead of a user space.
The request handle parameter was added to the QGYOLJBL API, so that a distinct value is associated with the list. This value ensures that a request for more messages from the list (call QGYGTLE API) is sent to the correct list.
The total records parameter was added to the QGYOLJBL API, so that the caller of the API knows the size of the list from which information can be requested.
The records returned parameter was added to the QGYOLJBL API. Now if the request is for X records and only Y records exist (where Y < X), the requestor is aware that it did not get the number of records requested.
The amount of information returned by the QMHLJOBL API is limited by the size of a user space, approximately 16MB. The list created by the QGYOLJBL API can contain up to 2GB of information
Saturday, March 13, 2010
as400 commands and APIs you'll use for handling messages
The following list of ibm as400 commands and APIs are used for receiving/sending messages. The APIs can be used in any program, whereas the commands are restricted to CL.
1. SNDPGMMSG. The Send Program Message (SNDPGMMSG) command sends a message to a named message queue or to a call message queue. A call message queue can be the *EXT external message queue or a message queue associated with a call stack entry. Each time a program or procedure is called a new message queue is associated with its call stack entry. The message queue is identified by the name of its associated program or procedure.
2. QMHSNDPM. The Send Program Message (QMHSNDPM) API sends a as400 message to a call message queue or the external message queue. (The external message queue is the part of the job message queue that handles messages between an interactive job and the work station user. It is not associated with a specific call stack entry.) This API allows the current call stack entry to send a message to its caller, a previous caller, or itself.
3. QMHSNDM. The Send Nonprogram Message (QMHSNDM) API sends a message to a nonprogram message queue so your program can communicate with another job or user
4. RCVMSG. The Receive Message (RCVMSG) command is used by a program to receive a message previously sent to a message queue. The RCVMSG command receives messages from a job message queue (a message queue associated with a call stack entry or the external message queue (*EXT)), or from a named message queue. The program can receive a message from a message queue associated with its own call stack entry or from a message queue associated with another call stack entry
5. QMHRCVPM. The Receive Program Message (QMHRCVPM) API receives a message from a call message queue or external message queue and returns information describing the message
6. QMHRCVM. The Receive Nonprogram Message (QMHRCVM) API receives a message from a nonprogram message queue. To receive a message from a program message queue or from the external message queue, see Receive Program Message API.
More tips on handling messages on IBM as400 to follow...
as400 images
1. SNDPGMMSG. The Send Program Message (SNDPGMMSG) command sends a message to a named message queue or to a call message queue. A call message queue can be the *EXT external message queue or a message queue associated with a call stack entry. Each time a program or procedure is called a new message queue is associated with its call stack entry. The message queue is identified by the name of its associated program or procedure.
2. QMHSNDPM. The Send Program Message (QMHSNDPM) API sends a as400 message to a call message queue or the external message queue. (The external message queue is the part of the job message queue that handles messages between an interactive job and the work station user. It is not associated with a specific call stack entry.) This API allows the current call stack entry to send a message to its caller, a previous caller, or itself.
3. QMHSNDM. The Send Nonprogram Message (QMHSNDM) API sends a message to a nonprogram message queue so your program can communicate with another job or user
4. RCVMSG. The Receive Message (RCVMSG) command is used by a program to receive a message previously sent to a message queue. The RCVMSG command receives messages from a job message queue (a message queue associated with a call stack entry or the external message queue (*EXT)), or from a named message queue. The program can receive a message from a message queue associated with its own call stack entry or from a message queue associated with another call stack entry
5. QMHRCVPM. The Receive Program Message (QMHRCVPM) API receives a message from a call message queue or external message queue and returns information describing the message
6. QMHRCVM. The Receive Nonprogram Message (QMHRCVM) API receives a message from a nonprogram message queue. To receive a message from a program message queue or from the external message queue, see Receive Program Message API.
More tips on handling messages on IBM as400 to follow...
as400 images
Saturday, March 06, 2010
Subscribe to:
Posts (Atom)
Subscribe To as400 blog
Posts
All Comments