Please post your AS/400 job requirements here.
Read More...
Tuesday, December 29, 2009
AS/400 Programmer Contacts
For AS/400 Job Seekers, please post your qualifications here:
Sample Information
Name : Juan Dela Cruz
Location : Philippines
E-Mail : jdelacruz@gmail.com
Skills : RPG, RPG-ILE, CL, CL-ILE, SQL/400
Yrs. of Exp. : 5 yrs.
Availability : ASAP
Read More...
Sample Information
Name : Juan Dela Cruz
Location : Philippines
E-Mail : jdelacruz@gmail.com
Skills : RPG, RPG-ILE, CL, CL-ILE, SQL/400
Yrs. of Exp. : 5 yrs.
Availability : ASAP
Read More...
Monday, December 14, 2009
(Tips) AS/400 Free Connection
FREE AS/400 connection, SIGNUP now!
RZKH is a modern and independant Consulting and service company. They offer service in whole germany, austria, switzerland and adjoining countries. Their main business activities cover the IBM system i (also known as iSeries or AS/400).
To connect to the AS/400 you may DOWNLOAD Mochasoft's TN 5250 client for FREE (30 days) or you may use any 5250 telnet program.
Read More...
Thursday, December 10, 2009
(Q&A) How To Display Library Contents Using CL?
(From : aaaadddd1)
Does anyone knows a process or CL that uses DSPLIB to display the contents of a library and their attributes, such as their size and last used date, followed by creating a file that I can query against?
My goal is to first list the name, last used date and size of all objects in a production library and delete objects that have not been used in more than a year.
You can do this by using:
DSPOBJD OBJ(MYLIBRARY/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE)OUTFILE(QTEMP/MYFILE)
Where:
MYLIBRARY - the desired library.
MYFILE - Outfile Filename.
You can use these fields as reference:
ODLBNM - Libray
ODOBNM - Object Name
ODOBTP - Object Type
ODOBSZ - Object Size
ODCDAT - Creation Date
ODCTIM - Creation Time
ODOBOW - Object Owner
ODLDAT - Change Date
ODLTIM - Change Time
I hope this will help you.
Read More...
Does anyone knows a process or CL that uses DSPLIB to display the contents of a library and their attributes, such as their size and last used date, followed by creating a file that I can query against?
My goal is to first list the name, last used date and size of all objects in a production library and delete objects that have not been used in more than a year.
You can do this by using:
DSPOBJD OBJ(MYLIBRARY/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE)OUTFILE(QTEMP/MYFILE)
Where:
MYLIBRARY - the desired library.
MYFILE - Outfile Filename.
You can use these fields as reference:
ODLBNM - Libray
ODOBNM - Object Name
ODOBTP - Object Type
ODOBSZ - Object Size
ODCDAT - Creation Date
ODCTIM - Creation Time
ODOBOW - Object Owner
ODLDAT - Change Date
ODLTIM - Change Time
I hope this will help you.
Read More...
Wednesday, November 25, 2009
(AS/400 For Dummies) Creating Physical File
We have created source physical files, we can now start creating Physical Files (PF). What is a Physical File? A physical file is a database file that stores application data. It contains a description of how data is to be presented to or received from a program and how data is actually stored in the database. A physical file consists of fixed-length records that can have variable-length fields. Physical files contain one record format and one or more members. From the perspective of the SQL interface, physical files are identical to tables.
Ways to Create Physical Files (PF):
1) Creating PF with DDS.
2) Creating PF without DDS.
3) Creating PF using *OUTFILE parameter.
4) Creating PF using SQL.
5) Using CPYF or CRTDUBOBJ commands.
Method 1: Creating PF with DDS (Data Description Specifications).
Physical Files have 4 DDS levels;
1) File Level DDS. It give the system information about the entire file. For example, you can specify whether all the key field values in the file must be unique.
2) Record format level DDS. It give the system information about a specific record format in the file. For example, when you describe a logical file record format, you can specify the physical file that it is based on.
3) Field level DDS. It give the system information about individual fields in the record format. For example, you can specify the name and attributes of each field.
4) Key field level DDS. It give the system information about the key fields for the file. For example, you can specify which fields in the record format are to be used as key fields.
For us to be able to understand more about these levels, let us start creating our PF.
File Definition:
Field Name, Description, Data Type, Length
P1LNME, Last Name, Character, 20
P1FNME, First Name, Character, 20
P1MINI, Middle Initial, Character, 1
P1AGE, Age Numeric, 2
P1CCDE, Course Code, Character, 5
P1CRDT, Create Date, Numeric, 8
P1CRTM, Create Time, Numeric, 6
P1UPDT, Update Date, Numeric, 8
P1UPTM, Update Time, Numeric, 6
note:
- PF is keyed by Last Name, First Name and Middle Initial.
- Records should be UNIQUE.
First in the command line type STRSEU then press F4 key. For Source File parameter we need to key in "QDDSSRC" (this is the source physical file that will hold PF and LF sources). Then for the Library parameter just input your library, for this tutorial i'll input "MyLibrary". For Source Member just enter "PFILE01" and then for Source Type parameter just enter "PF". Then for the Text Description parameter input "My Very First Physical File". Your screen should look like this:
If you are done entering those parameters, just press ENTER key to proceed with DDS entry.
Now, let us create the 4 DDS levels of PF.
1)File level DDS. In position 6 of the SEU body enter "A" then press F4. You'll have a screen like this one:
Then in the Functions column type "UNIQUE" then press ENTER key. This line will mark as our file level DDS.
2) Record level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "R" (to indicate a record format name), then on the Name column input "PFILER1" (this one is your record format name). After you are done hit the ENTER key.
3) Field level DDS. In position 6 of the SEU body enter "A" then press F4, for the Name Column enter "P1LNME", Length Column input "20", Data Type Column enter "A", and for the Functions Column input "TEXT('LAST NAME')". Repeat what we have entered for the remaining 8 file fields. For data types; "A" represents character field, "S" (zoned decimal) as numeric field. For numeric field, Decimal Positions Column should have a value, for this exercise just enter "0" for all numeric fields. TEXT keyword is used to enter field descriptions.
4) Key field level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "K" (to indicate a key field), then on the Name column input "P1LNME" (this represents your first key field). After you are done hit the ENTER key. Then do this again for the 2 remaining key fields.
After you are done your screen should look like this:
At the SEU command line on the top, just type FILE to save and exit SEU. Then to compile your PF just type this in the command line:
CRTPF FILE(MYLIBRARY/PFILE01) SRCFILE(MYLIBRARY/QDDSSRC) SRCMBR(PFILE01)
Method 2: Creating PF without DDS (Data Description Specifications).
We can create a physical file without DDS. This file will only contain 1 (one) field and by default the field will get the name of the PF. Here's the steps on how to create this type of PF:
a) Type CRTPF in the command line, then press F4.
b) Enter the desired file name in the FILE parameter.
c) Input your desired library.
d) Specify record/field length in the RCDLEN parameter.
e) Press ENTER key.
Your command should look like this one:
CRTPF FILE(QTEMP/MYFILE) RCDLEN(500)
Method 3: Creating PF using *OUTFILE parameter.
For this type of method, we can create PF using DSP command and then set the OUTPUT parameter to *OUTFILE. Here are some of the DSP commands that i normally use to create a PF:
1) DSPFD. I use this command to get file level information of a file and place the information inside a PF.
Command:
DSPFD FILE(MYLIBRARY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
2) DSPFFD. I use this command to get field level information of a file and place the information inside a PF.
Command:
DSPFFD FILE(MYLIBRAY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
3) DSPPGMREF. I use this command to get a list of system objects that my program is referencing and place the list inside a PF.
Command:
DSPPGMREF PGM(MYLIBRARY/MYPROGRAM) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
4) DSPDBR. I use this command to get a list of relational information about database files and place the list inside a PF.
Command:
DSPDBR FILE(MYLIBRARY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
5) DSPFLR. I use this command to get a list of folders/documents and place the list inside a PF.
Command:
DSPFLR FLR(MYFOLDER) TYPE(*DOC) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
note: OUTFILE(QTEMP/MYOUTFILE) - contains the library (QTEMP) and the filename (MYOUTFILE) of the PF that I want to create.
Method 4: Creating PF using SQL.
If you are familiar with SQL, you can also create a PF using this method. Type STRSQL in the command line then press ENTER key. You can now enter SQL statement to create a table.
Sample SQL statement:
CREATE TABLE QTEMP/MYFILE
(FLD1 CHARACTER (3) NOT NULL WITH DEFAULT,
FLD2 NUMERIC (4,0) NOT NULL WITH DEFAULT)
Method 5: Using CPYF or CRTDUBOBJ commands.
You can also create a PF by copying or duplicating existing physical files.
1) Using CPYF:
CPYF FROMFILE(MYLIBRARY/MYFILE) TOFILE(QTEMP/MYCOPY) CRTFILE(*YES)
2) Using CRTDUPOBJ:
CRTDUPOBJ OBJ(MYFILE) FROMLIB(MYLIBRARY) OBJTYPE(*FILE) TOLIB(QTEMP) NEWOBJ(MYOBJECT)
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Ways to Create Physical Files (PF):
1) Creating PF with DDS.
2) Creating PF without DDS.
3) Creating PF using *OUTFILE parameter.
4) Creating PF using SQL.
5) Using CPYF or CRTDUBOBJ commands.
Method 1: Creating PF with DDS (Data Description Specifications).
Physical Files have 4 DDS levels;
1) File Level DDS. It give the system information about the entire file. For example, you can specify whether all the key field values in the file must be unique.
2) Record format level DDS. It give the system information about a specific record format in the file. For example, when you describe a logical file record format, you can specify the physical file that it is based on.
3) Field level DDS. It give the system information about individual fields in the record format. For example, you can specify the name and attributes of each field.
4) Key field level DDS. It give the system information about the key fields for the file. For example, you can specify which fields in the record format are to be used as key fields.
For us to be able to understand more about these levels, let us start creating our PF.
File Definition:
Field Name, Description, Data Type, Length
P1LNME, Last Name, Character, 20
P1FNME, First Name, Character, 20
P1MINI, Middle Initial, Character, 1
P1AGE, Age Numeric, 2
P1CCDE, Course Code, Character, 5
P1CRDT, Create Date, Numeric, 8
P1CRTM, Create Time, Numeric, 6
P1UPDT, Update Date, Numeric, 8
P1UPTM, Update Time, Numeric, 6
note:
- PF is keyed by Last Name, First Name and Middle Initial.
- Records should be UNIQUE.
First in the command line type STRSEU then press F4 key. For Source File parameter we need to key in "QDDSSRC" (this is the source physical file that will hold PF and LF sources). Then for the Library parameter just input your library, for this tutorial i'll input "MyLibrary". For Source Member just enter "PFILE01" and then for Source Type parameter just enter "PF". Then for the Text Description parameter input "My Very First Physical File". Your screen should look like this:
If you are done entering those parameters, just press ENTER key to proceed with DDS entry.
Now, let us create the 4 DDS levels of PF.
1)File level DDS. In position 6 of the SEU body enter "A" then press F4. You'll have a screen like this one:
Then in the Functions column type "UNIQUE" then press ENTER key. This line will mark as our file level DDS.
2) Record level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "R" (to indicate a record format name), then on the Name column input "PFILER1" (this one is your record format name). After you are done hit the ENTER key.
3) Field level DDS. In position 6 of the SEU body enter "A" then press F4, for the Name Column enter "P1LNME", Length Column input "20", Data Type Column enter "A", and for the Functions Column input "TEXT('LAST NAME')". Repeat what we have entered for the remaining 8 file fields. For data types; "A" represents character field, "S" (zoned decimal) as numeric field. For numeric field, Decimal Positions Column should have a value, for this exercise just enter "0" for all numeric fields. TEXT keyword is used to enter field descriptions.
4) Key field level DDS. In position 6 of the SEU body enter "A" then press F4, for Name Type Column enter "K" (to indicate a key field), then on the Name column input "P1LNME" (this represents your first key field). After you are done hit the ENTER key. Then do this again for the 2 remaining key fields.
After you are done your screen should look like this:
At the SEU command line on the top, just type FILE to save and exit SEU. Then to compile your PF just type this in the command line:
CRTPF FILE(MYLIBRARY/PFILE01) SRCFILE(MYLIBRARY/QDDSSRC) SRCMBR(PFILE01)
Method 2: Creating PF without DDS (Data Description Specifications).
We can create a physical file without DDS. This file will only contain 1 (one) field and by default the field will get the name of the PF. Here's the steps on how to create this type of PF:
a) Type CRTPF in the command line, then press F4.
b) Enter the desired file name in the FILE parameter.
c) Input your desired library.
d) Specify record/field length in the RCDLEN parameter.
e) Press ENTER key.
Your command should look like this one:
CRTPF FILE(QTEMP/MYFILE) RCDLEN(500)
Method 3: Creating PF using *OUTFILE parameter.
For this type of method, we can create PF using DSP command and then set the OUTPUT parameter to *OUTFILE. Here are some of the DSP commands that i normally use to create a PF:
1) DSPFD. I use this command to get file level information of a file and place the information inside a PF.
Command:
DSPFD FILE(MYLIBRARY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
2) DSPFFD. I use this command to get field level information of a file and place the information inside a PF.
Command:
DSPFFD FILE(MYLIBRAY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
3) DSPPGMREF. I use this command to get a list of system objects that my program is referencing and place the list inside a PF.
Command:
DSPPGMREF PGM(MYLIBRARY/MYPROGRAM) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
4) DSPDBR. I use this command to get a list of relational information about database files and place the list inside a PF.
Command:
DSPDBR FILE(MYLIBRARY/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
5) DSPFLR. I use this command to get a list of folders/documents and place the list inside a PF.
Command:
DSPFLR FLR(MYFOLDER) TYPE(*DOC) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYOUTFILE)
note: OUTFILE(QTEMP/MYOUTFILE) - contains the library (QTEMP) and the filename (MYOUTFILE) of the PF that I want to create.
Method 4: Creating PF using SQL.
If you are familiar with SQL, you can also create a PF using this method. Type STRSQL in the command line then press ENTER key. You can now enter SQL statement to create a table.
Sample SQL statement:
CREATE TABLE QTEMP/MYFILE
(FLD1 CHARACTER (3) NOT NULL WITH DEFAULT,
FLD2 NUMERIC (4,0) NOT NULL WITH DEFAULT)
Method 5: Using CPYF or CRTDUBOBJ commands.
You can also create a PF by copying or duplicating existing physical files.
1) Using CPYF:
CPYF FROMFILE(MYLIBRARY/MYFILE) TOFILE(QTEMP/MYCOPY) CRTFILE(*YES)
2) Using CRTDUPOBJ:
CRTDUPOBJ OBJ(MYFILE) FROMLIB(MYLIBRARY) OBJTYPE(*FILE) TOLIB(QTEMP) NEWOBJ(MYOBJECT)
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Friday, October 16, 2009
(Query/400 Tutorial) Selecting definition steps when defining a Query/400 query
When you specify option 1 (Create) on the Work with Queries display, you are shown the Define the Query display (another key display) where you select one, several, or all of the 11 definition steps (options) needed to define your query.
The only definition step that you are required to select is Specify file selections. You do not have to select all of these definition steps—use only those that you need. Most of these steps do not have to be done in a specific order (although the order shown is recommended when you need to use most of them). Each step you select is a separate process that shows you one or more displays as you need them.
The only definition step that you are required to select is Specify file selections. You do not have to select all of these definition steps—use only those that you need. Most of these steps do not have to be done in a specific order (although the order shown is recommended when you need to use most of them). Each step you select is a separate process that shows you one or more displays as you need them.
The Define the Query display is the primary display from which you start defining your query.
From this display, you can select options that define, generally speaking, the four major parts of a complete query definition:
- The first six options define the query itself, including the files you want to query, the fields to be used in each file, and the records to be selected.
- The next three options define what the report is to look like, including which columns are to be summarized and when (using report breaks).
- The second to last option defines where the report is to go and what values are to be used when it is sent there.
- The last option defines how numeric calculation results are processed and if substitution
characters are allowed during the conversion of character fields.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Wednesday, September 30, 2009
(Query/400 Tutorial) Starting Query/400 query definition
You start query definition by selecting option 1 (Create) on the Work with Queries display and, optionally, by specifying the name of the query you want to create.
To specify a query name, you can type the name of the query (Query prompt) that you want to define, and you can specify the name of the library (Library prompt) in which it is to be stored. Or, you can look at a list of query names or library names to select the query name and the library name you want to use.
For example, you might specify CUSNAMQRY as the name of a query definition that you would use to query the CUSTNAME file. If you do not specify a library name, the query is stored in the library identified in the Library prompt.
If you are creating a query (definition), you do not need to name it unless you want to save it for later use. (The query does not exist as a definition object on the system until you save it.) If you do name it, use the normal rule for naming objects, which follows:
The query name must begin with an alphabetic character (A through Z, $, #, or @) and can be followed by no more than 9 alphanumeric characters (A through Z, 0 through 9, $, #, @, ., or _).
Note: To use this query in a multilingual environment, use A-Z or 1-9.
Because most system-supplied objects on the AS/400 system begin with Q, your query names should not start with a Q.
If you position the cursor on the Query prompt and press F4 (Prompt) to show a list, it contains the names of all the queries that are in the library (or libraries) indicated by the Library prompt. You can get a smaller list by typing a generic name in the Query prompt before you press F4. The generic name shows in the Subset prompt when the list is shown.
When you are creating a query, you can check this list to see what names are already used before you choose a new name. Type the new name in the first list position (in its input field) and type a 1 next to it.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
To specify a query name, you can type the name of the query (Query prompt) that you want to define, and you can specify the name of the library (Library prompt) in which it is to be stored. Or, you can look at a list of query names or library names to select the query name and the library name you want to use.
For example, you might specify CUSNAMQRY as the name of a query definition that you would use to query the CUSTNAME file. If you do not specify a library name, the query is stored in the library identified in the Library prompt.
If you are creating a query (definition), you do not need to name it unless you want to save it for later use. (The query does not exist as a definition object on the system until you save it.) If you do name it, use the normal rule for naming objects, which follows:
The query name must begin with an alphabetic character (A through Z, $, #, or @) and can be followed by no more than 9 alphanumeric characters (A through Z, 0 through 9, $, #, @, ., or _).
Note: To use this query in a multilingual environment, use A-Z or 1-9.
Because most system-supplied objects on the AS/400 system begin with Q, your query names should not start with a Q.
If you position the cursor on the Query prompt and press F4 (Prompt) to show a list, it contains the names of all the queries that are in the library (or libraries) indicated by the Library prompt. You can get a smaller list by typing a generic name in the Query prompt before you press F4. The generic name shows in the Subset prompt when the list is shown.
When you are creating a query, you can check this list to see what names are already used before you choose a new name. Type the new name in the first list position (in its input field) and type a 1 next to it.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Tuesday, September 29, 2009
(Q&A) SAVLIB Error
I would like to know how to not encounter error when using savlib command
SAVLIB LIB(&LIBL) the &LIBL: as the parm and has 500 length (ex. 10 libraries inside)..
in the command prompt when SAVLIB is used:
LIB should appear as this :
MLIB
XLIB
SLIB
but.. when the RPG calls the CL it becomes
'MLIB XLIB SLIB' -- it is transferred as one constant how can i pass the parameter and let the command be as shown as above ?
(From : Lui)
Answer:
You can use QCMDEXC to run the command. Your command string parameter should contain: "SAVLIB LIB(LUILIB LEALIB XXXLIB)", you can obtain this by using series of string manipulation command then don't forget to pass the command string length.
Read More...
SAVLIB LIB(&LIBL) the &LIBL: as the parm and has 500 length (ex. 10 libraries inside)..
in the command prompt when SAVLIB is used:
LIB should appear as this :
MLIB
XLIB
SLIB
but.. when the RPG calls the CL it becomes
'MLIB XLIB SLIB' -- it is transferred as one constant how can i pass the parameter and let the command be as shown as above ?
(From : Lui)
Answer:
You can use QCMDEXC to run the command. Your command string parameter should contain: "SAVLIB LIB(LUILIB LEALIB XXXLIB)", you can obtain this by using series of string manipulation command then don't forget to pass the command string length.
Read More...
Friday, September 25, 2009
(Query/400 Tutorial) Getting Started With Query/400
Getting started with Query/400
You can access Query in a variety of ways. To use Query, you can do any of the following:
- Use the Work With Query (WRKQRY) command by typing WRKQRY and pressing the Enter key. This
causes the Work with Queries display to appear.
-Select option 3 (Decision support) on the Office Tasks (OFCTSK) menu. From the Decision Support display, select option 2 (Query). This causes the Query menu to appear.
- Select option 8 (Decision support) on the Office menu. From the Decision Support display, select option 2 (Query). This causes the Query menu to appear.
- Use the Start Query (STRQRY) command by typing STRQRY and pressing the Enter key. This causes the Query menu to appear. Typing GO QUERY and pressing the Enter key has the same effect.
- While using OfficeVision, press F17 on the edit display and then select option 1 (Query). This causes the Work with Queries display to appear.
- While using the DisplayWrite 4 program, press the control key and F6 on the edit display. Select option 2 (Get query file option). This is explained in detail in the DisplayWrite 4 or DisplayWrite 5 help information. This causes the Work with Queries display to appear.
You can access Query in a variety of ways. To use Query, you can do any of the following:
- Use the Work With Query (WRKQRY) command by typing WRKQRY and pressing the Enter key. This
causes the Work with Queries display to appear.
-Select option 3 (Decision support) on the Office Tasks (OFCTSK) menu. From the Decision Support display, select option 2 (Query). This causes the Query menu to appear.
- Select option 8 (Decision support) on the Office menu. From the Decision Support display, select option 2 (Query). This causes the Query menu to appear.
- Use the Start Query (STRQRY) command by typing STRQRY and pressing the Enter key. This causes the Query menu to appear. Typing GO QUERY and pressing the Enter key has the same effect.
- While using OfficeVision, press F17 on the edit display and then select option 1 (Query). This causes the Work with Queries display to appear.
- While using the DisplayWrite 4 program, press the control key and F6 on the edit display. Select option 2 (Get query file option). This is explained in detail in the DisplayWrite 4 or DisplayWrite 5 help information. This causes the Work with Queries display to appear.
From the Query menu, you can choose to work with queries, run a query, delete a query, work with files, or do an office-related task.
From the Work with Queries display you can:
- Create, change, copy, delete, display, print, or run a query if you came from the Query menu or the WRKQRY command.
- Create,change, copy, delete, or display a query definition, or you can do direct merge, column list merge, or multicopy merge if you came from OfficeVision.
- Create, change, copy, delete, or display a query definition, or you can do direct merge if you came from the DisplayWrite 4 or DisplayWrite 5 program.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
From the Work with Queries display you can:
- Create, change, copy, delete, display, print, or run a query if you came from the Query menu or the WRKQRY command.
- Create,change, copy, delete, or display a query definition, or you can do direct merge, column list merge, or multicopy merge if you came from OfficeVision.
- Create, change, copy, delete, or display a query definition, or you can do direct merge if you came from the DisplayWrite 4 or DisplayWrite 5 program.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Thursday, September 24, 2009
(Query/400 Tutorial) Query/400 fundamentals
Several elements on your system organize and store information, or data, so that you and other system users can work with it to get the results that you need. The following topics introduce those elements, tell you about them and how they relate to you and Query, and direct you to other publications where you can find more information.
Files, fields, and record formats in Query/400
----------------------------------------------
Information, or data, is organized and stored on your system in various forms, primarily in objects called database files (usually referred to as just files). A file contains individual units of information, called records, that each contain related pieces of data. Each piece of information in a record is called a field, and how the fields are organized is defined in a record format (often just called a format).
When you run a query to produce a report, Query uses the files, fields, and record formats to get the information you want from the database, in the form of records, and uses those records to produce a query report.
Double-byte character set (DBCS) fields in Query/400
----------------------------------------------------
Some countries use pictographs or symbolic characters in their language. DBCS fields must be used for such data. As a general rule, if your national language uses single-byte character set (SBCS) characters, your files do not contain DBCS data. You can ignore any on-line help information that refers to DBCS data.
Notes:
1. To properly display DBCS data, you need a DBCS-capable display.
2. In Query, the following naming convention is used for DBCS data:
- Character data refers to both SBCS and DBCS character data.
- DBCS data refers to any type of DBCS data, including bracketed-DBCS and DBCS-graphic data types.
- Bracketed DBCS refers to DBCS-open, DBCS-either, or DBCS-only data types.
UCS2 level 1 character set support in Query/400
-----------------------------------------------
UCS2 Level 1 is a 16-bit encoding for graphic characters. When doing business in a worldwide environment you need the ability to enter and process data from more than one national language. For example, a list of customer names may contain German, Greek, English, and Thai characters that must be printed or displayed on the same device at the same time.
Query/400 treats UCS2-graphic data the same as GRAPHIC or VARGRAPHIC data. A UCS2-graphic field is a DBCS-graphic field tagged with a UCS2 CCSID.
The VARCHAR and VARGRAPHIC functions help you write queries that include UCS2 data.
Data definition languages and utilities support in Query/400
------------------------------------------------------------
Query can query data in files that are created using different data definition languages or products. Although the description given above applies to all the files on your system, the names or concepts that might be used depend on the programming language or product (like IDDU, DDS, and DB2 UDB for iSeries) that is used to define the files.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Files, fields, and record formats in Query/400
----------------------------------------------
Information, or data, is organized and stored on your system in various forms, primarily in objects called database files (usually referred to as just files). A file contains individual units of information, called records, that each contain related pieces of data. Each piece of information in a record is called a field, and how the fields are organized is defined in a record format (often just called a format).
When you run a query to produce a report, Query uses the files, fields, and record formats to get the information you want from the database, in the form of records, and uses those records to produce a query report.
Double-byte character set (DBCS) fields in Query/400
----------------------------------------------------
Some countries use pictographs or symbolic characters in their language. DBCS fields must be used for such data. As a general rule, if your national language uses single-byte character set (SBCS) characters, your files do not contain DBCS data. You can ignore any on-line help information that refers to DBCS data.
Notes:
1. To properly display DBCS data, you need a DBCS-capable display.
2. In Query, the following naming convention is used for DBCS data:
- Character data refers to both SBCS and DBCS character data.
- DBCS data refers to any type of DBCS data, including bracketed-DBCS and DBCS-graphic data types.
- Bracketed DBCS refers to DBCS-open, DBCS-either, or DBCS-only data types.
UCS2 level 1 character set support in Query/400
-----------------------------------------------
UCS2 Level 1 is a 16-bit encoding for graphic characters. When doing business in a worldwide environment you need the ability to enter and process data from more than one national language. For example, a list of customer names may contain German, Greek, English, and Thai characters that must be printed or displayed on the same device at the same time.
Query/400 treats UCS2-graphic data the same as GRAPHIC or VARGRAPHIC data. A UCS2-graphic field is a DBCS-graphic field tagged with a UCS2 CCSID.
The VARCHAR and VARGRAPHIC functions help you write queries that include UCS2 data.
Data definition languages and utilities support in Query/400
------------------------------------------------------------
Query can query data in files that are created using different data definition languages or products. Although the description given above applies to all the files on your system, the names or concepts that might be used depend on the programming language or product (like IDDU, DDS, and DB2 UDB for iSeries) that is used to define the files.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
(Query/400 Tutorial) What is Query/400?
Query/400 is an IBM licensed program and a decision support utility that can be used to obtain information from the AS/400* (AS/400*) database. It can obtain information from any database files that have been defined on the system using Operating System/400* (OS/400*) data description specifications (DDS), the OS/400 interactive data definition utility (IDDU), or the IBM* Structured Query Language/400 (DB2 UDB for iSeries*) program.
You use Query to select, arrange, and analyze information (data) stored in one or more database files to produce reports and other data files. You can create your own query definitions and then run them, you can run existing queries that you did not create, or you can even run a default query against a particular database file (using an unnamed query). You determine what data the query is to retrieve, the format of the report, and whether it should be displayed, printed, or sent to another database file.
You can use Query to obtain information from a single file or a combined set of up to 32 files. You can select all the fields, or a few of the fields and organize them as you want them to appear in the type of output chosen. You can have all records in the files included in the output, or you can select only a few to be included, using record selection tests. These and other functions are described in detail later.
This tutorial begins by introducing basic information about Query, and then it introduces all the major tasks (such as creating, displaying, or running query) that can be done using Query, including the data/text merge options that can be used by IBM OfficeVision*, DisplayWrite* 4, and DisplayWrite 5 users.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Wednesday, September 9, 2009
(Q&A) How To Convert AS/400 File To Excel Compatible Format
I just want to ask on how can i go about transfering a flat file to an excel file, which uses comma (,) as a delimeter. And how about if it's from a physical file and i want to transfer it to an excel file? Can you also provide a link on how to do it or maybe you can give me a step by step sample program.
(From : kNOw_wELL)
For the first question you can use FTP to transfer your flat file from AS/400 to your desktop. Use your desktop's dos shell to manually FTP your file. You can use the following commands:
a. FTP (AS/400 IP Address)
b. Enter AS/400 Username
c. Password AS/400 Password
d. Binary
e. Get MYLIB/MYFLATFILE C:\MYFILE.CSV
f. Quit
Now if you want to transfer your PF to your desktop, you need to convert your PF first into a delimeted flatfile format. You can concatenate (,) or (X'05') delimeter inbetween fields then write it in a flatfile. Or you can use CPYTOIMPF to automatically convert your PF into delimeted flatfile format.
Sample:
CPYTOIMPF FROMFILE(MYLIB/MYPF) TOFILE(MYLIB/MYFLATFILE) MBROPT(*ADD) STRDLM(*NONE) FLDDLM(&HEX)
Where &Hex contains hexadecimal constant X'05'.
After converting your PF into flatfile you can now use FTP.
Note:
- For comma delimeter, use .CSV as your file extension format.
- For X'05' delimeter, use .XLS as your file extension format.
For batch FTP, first create a simple CL program. Let's call ours FTPSCRIPT. In it we'll
have these lines:
PGM PARM(&RMTSYSTEM)
DCL VAR(&RMTSYSTEM) TYPE(*CHAR) LEN(200)
OVRDBF FILE(INPUT) TOFILE(MYLIB/FTPSCRIPT)
OVRDBF FILE(OUTPUT) TOFILE(MYLIB/FTPOUTPUT)
FTP RMTSYS(&RMTSYSTEM)
DLTOVR FILE(INPUT)
DLTOVR FILE(OUTPUT)
ENDPGM
The file MYLIB/FTPSCRIPT should be a one field file. The record length is pretty flexible.
The file MYLIB/FTPOUTPUT should also be a one field file. Again, the record length is pretty flexible. In the file MYLIB/FTPSCRIPT put the following:
user myuserid mypassword
cd mylib
quote rcmd crtpf mylib/myfile rcdlen(80)
put myfile.txt myfile
quote rcmd runpost
quit
Now when you type CALL FTPSCRIPT it will run the CL program FTPSCRIPT, which will use the FTPSCRIPT file. You can use DSPPFM/RUNQRY or some other utility to check the FTPOUTPUT file for the status of the transmission.
Read More...
(From : kNOw_wELL)
For the first question you can use FTP to transfer your flat file from AS/400 to your desktop. Use your desktop's dos shell to manually FTP your file. You can use the following commands:
a. FTP (AS/400 IP Address)
b. Enter AS/400 Username
c. Password AS/400 Password
d. Binary
e. Get MYLIB/MYFLATFILE C:\MYFILE.CSV
f. Quit
Now if you want to transfer your PF to your desktop, you need to convert your PF first into a delimeted flatfile format. You can concatenate (,) or (X'05') delimeter inbetween fields then write it in a flatfile. Or you can use CPYTOIMPF to automatically convert your PF into delimeted flatfile format.
Sample:
CPYTOIMPF FROMFILE(MYLIB/MYPF) TOFILE(MYLIB/MYFLATFILE) MBROPT(*ADD) STRDLM(*NONE) FLDDLM(&HEX)
Where &Hex contains hexadecimal constant X'05'.
After converting your PF into flatfile you can now use FTP.
Note:
- For comma delimeter, use .CSV as your file extension format.
- For X'05' delimeter, use .XLS as your file extension format.
For batch FTP, first create a simple CL program. Let's call ours FTPSCRIPT. In it we'll
have these lines:
PGM PARM(&RMTSYSTEM)
DCL VAR(&RMTSYSTEM) TYPE(*CHAR) LEN(200)
OVRDBF FILE(INPUT) TOFILE(MYLIB/FTPSCRIPT)
OVRDBF FILE(OUTPUT) TOFILE(MYLIB/FTPOUTPUT)
FTP RMTSYS(&RMTSYSTEM)
DLTOVR FILE(INPUT)
DLTOVR FILE(OUTPUT)
ENDPGM
The file MYLIB/FTPSCRIPT should be a one field file. The record length is pretty flexible.
The file MYLIB/FTPOUTPUT should also be a one field file. Again, the record length is pretty flexible. In the file MYLIB/FTPSCRIPT put the following:
user myuserid mypassword
cd mylib
quote rcmd crtpf mylib/myfile rcdlen(80)
put myfile.txt myfile
quote rcmd runpost
quit
Now when you type CALL FTPSCRIPT it will run the CL program FTPSCRIPT, which will use the FTPSCRIPT file. You can use DSPPFM/RUNQRY or some other utility to check the FTPOUTPUT file for the status of the transmission.
Read More...
Wednesday, August 26, 2009
(Tips) Copying data using Client Access Data Transfer Function
The Client Access Data Transfer applications have the advantage of an easy-to-follow graphical interface, and automatic numeric and character data conversion. However, Data Transfer requires the installation of the Client Access product and requires use of both PC and iSeries server resources and communications between the two.
If you have Client Access installed on the PC and your server, you can use the Data Transfer applications to transfer data between stream files and database files. You can also transfer data into a new database file based on an existing database file, into an externally-described database file, or into a new database file definition and file.
* Transfer data from a database file to a stream file.
* Transfer data from a stream file to a database file.
* Move data into a newly created database file definition and file.
* Create a format description file.
Transfer data from a database file to a stream file
To transfer a file from a database file to a stream file on your server:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer from AS/400 icon from the Client Access Express window.
4. Select the server you want to transfer from.
5. Select the file names, using the iSeries database library and file name to copy from, and the network drive for the location of the resulting stream file. You can also choose PC File Details to select the PC file format for the stream file. Data Transfer supports common PC file types, such as ASCII text, BIFF3, CSV, DIF, Tab-delimited Text, or WK4.
6. Click the Transfer data from AS/400 button to run the file transfer.
You can also perform this data movement in a batch job with the Data Transfer applications. Proceed as above, but select the File menu option to save the transfer request. The Data Transfer To AS/400 application creates a .DTT or a .TFR file. The Data Transfer from AS/400 application creates a .DTF or a .TTO file. In the Client Access directory, two programs can be run in batch from a command line:
* RTOPCB takes either a .DTF or a .TTO file as a parameter
* RFROMPCB takes either a .DTT or a .TFR file as a parameter
You can set either of these commands to run on a scheduled basis by using a scheduler application. For example, you can use the System Agent Tool (a part of the Microsoft Plus Pack) to specify the program to run (for instance, RTOPCB MYFILE.TTO) and the time at which you want to run the program.
Transfer data from a stream file to a database file
To transfer data from a stream file to a database file on your server:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer to AS/400 icon from the Client Access Express window.
4. Select the PC file name you want to transfer. For the PC file name, you can choose Browse for the network drive you assigned, and choose a stream file. You can also use a stream file located on the PC itself.
5. Select the server on which you want the externally described database file to be located.
6. Click the Transfer data to AS/400 button to run the file transfer.
Note: If you are moving data to an existing database file definition on the server, the Data Transfer To AS/400 application requires you to use an associated format description file (FDF). An FDF file describes the format of a stream file, and is created by the Data Transfer from AS/400 application when data is transferred from a database file to a stream file. To complete the transfer of data from a stream file to a database file, click the Transfer to AS/400 button. If an existing .FDF file is not available, you can quickly create an .FDF file.
You can also perform this data movement in a batch job with the Data Transfer applications. Proceed as above, but select the File menu option to save the transfer request. The Data Transfer To AS/400 application creates a .DTT or a .TFR file. The Data Transfer from AS/400 application creates a .DTF or a .TTO file. In the Client Access directory, two programs can be run in batch from a command line:
* RTOPCB takes either a .DTF or a .TTO file as a parameter
* RFROMPCB takes either a .DTT or a .TFR file as a parameter
You can set either of these commands to run on a scheduled basis by using a scheduler application. For example, you can use the System Agent Tool (a part of the Microsoft Plus Pack) to specify the program to run (for instance, RTOPCB MYFILE.TTO) and the time at which you want to run the program.
Move data into a newly created database file definition and file
To move data into a newly created database file definition and file:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer to AS/400 icon from the Client Access Express window.
4. Open the Tools menu of the Data Transfer to AS/400 application.
5. Select Create AS/400 database file.
A wizard will appear that allows you to create an new AS/400 database file from an existing PC file. You will be required to specify the name of the PC file from which the AS/400 file will be based, the name of the AS/400 file which to create, and several other necessary details. This tool parses a given stream file to determine the number, type, and size of the fields that are required in the resulting database file. The tool can then create the database file definition on your server.
Integrated File System Introduction
Create a format description file
If you are moving data to an existing database file definition on the server, the Data Transfer To AS/400 application requires you to use an associated format description file (FDF). An FDF file describes the format of a stream file, and is created by the Data Transfer from AS/400 application when data is transferred from a database file to a stream file. To create a .FDF file:
1. Create an externally described database file with a format that matches your source stream file (number of fields, types of data).
2. Create one temporary data record within the database file.
3. Use the Data Transfer from AS/400 function to create a stream file and its associated .FDF file from this database file.
4. Now, you can use the Data Transfer to AS/400, and specify this .FDF file with the source stream file you want to transfer.
Source : Iseries Information Center
Read More...
If you have Client Access installed on the PC and your server, you can use the Data Transfer applications to transfer data between stream files and database files. You can also transfer data into a new database file based on an existing database file, into an externally-described database file, or into a new database file definition and file.
* Transfer data from a database file to a stream file.
* Transfer data from a stream file to a database file.
* Move data into a newly created database file definition and file.
* Create a format description file.
Transfer data from a database file to a stream file
To transfer a file from a database file to a stream file on your server:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer from AS/400 icon from the Client Access Express window.
4. Select the server you want to transfer from.
5. Select the file names, using the iSeries database library and file name to copy from, and the network drive for the location of the resulting stream file. You can also choose PC File Details to select the PC file format for the stream file. Data Transfer supports common PC file types, such as ASCII text, BIFF3, CSV, DIF, Tab-delimited Text, or WK4.
6. Click the Transfer data from AS/400 button to run the file transfer.
You can also perform this data movement in a batch job with the Data Transfer applications. Proceed as above, but select the File menu option to save the transfer request. The Data Transfer To AS/400 application creates a .DTT or a .TFR file. The Data Transfer from AS/400 application creates a .DTF or a .TTO file. In the Client Access directory, two programs can be run in batch from a command line:
* RTOPCB takes either a .DTF or a .TTO file as a parameter
* RFROMPCB takes either a .DTT or a .TFR file as a parameter
You can set either of these commands to run on a scheduled basis by using a scheduler application. For example, you can use the System Agent Tool (a part of the Microsoft Plus Pack) to specify the program to run (for instance, RTOPCB MYFILE.TTO) and the time at which you want to run the program.
Transfer data from a stream file to a database file
To transfer data from a stream file to a database file on your server:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer to AS/400 icon from the Client Access Express window.
4. Select the PC file name you want to transfer. For the PC file name, you can choose Browse for the network drive you assigned, and choose a stream file. You can also use a stream file located on the PC itself.
5. Select the server on which you want the externally described database file to be located.
6. Click the Transfer data to AS/400 button to run the file transfer.
Note: If you are moving data to an existing database file definition on the server, the Data Transfer To AS/400 application requires you to use an associated format description file (FDF). An FDF file describes the format of a stream file, and is created by the Data Transfer from AS/400 application when data is transferred from a database file to a stream file. To complete the transfer of data from a stream file to a database file, click the Transfer to AS/400 button. If an existing .FDF file is not available, you can quickly create an .FDF file.
You can also perform this data movement in a batch job with the Data Transfer applications. Proceed as above, but select the File menu option to save the transfer request. The Data Transfer To AS/400 application creates a .DTT or a .TFR file. The Data Transfer from AS/400 application creates a .DTF or a .TTO file. In the Client Access directory, two programs can be run in batch from a command line:
* RTOPCB takes either a .DTF or a .TTO file as a parameter
* RFROMPCB takes either a .DTT or a .TFR file as a parameter
You can set either of these commands to run on a scheduled basis by using a scheduler application. For example, you can use the System Agent Tool (a part of the Microsoft Plus Pack) to specify the program to run (for instance, RTOPCB MYFILE.TTO) and the time at which you want to run the program.
Move data into a newly created database file definition and file
To move data into a newly created database file definition and file:
1. Establish a connection to the server.
2. Map a network drive to the appropriate path in the iSeries file system.
3. Select the Data Transfer to AS/400 icon from the Client Access Express window.
4. Open the Tools menu of the Data Transfer to AS/400 application.
5. Select Create AS/400 database file.
A wizard will appear that allows you to create an new AS/400 database file from an existing PC file. You will be required to specify the name of the PC file from which the AS/400 file will be based, the name of the AS/400 file which to create, and several other necessary details. This tool parses a given stream file to determine the number, type, and size of the fields that are required in the resulting database file. The tool can then create the database file definition on your server.
Integrated File System Introduction
Create a format description file
If you are moving data to an existing database file definition on the server, the Data Transfer To AS/400 application requires you to use an associated format description file (FDF). An FDF file describes the format of a stream file, and is created by the Data Transfer from AS/400 application when data is transferred from a database file to a stream file. To create a .FDF file:
1. Create an externally described database file with a format that matches your source stream file (number of fields, types of data).
2. Create one temporary data record within the database file.
3. Use the Data Transfer from AS/400 function to create a stream file and its associated .FDF file from this database file.
4. Now, you can use the Data Transfer to AS/400, and specify this .FDF file with the source stream file you want to transfer.
Source : Iseries Information Center
Monday, August 17, 2009
(Tips) AS/400 Built In Functions %EOF
%EOF returns ’1’ if the most recent read operation or write to a subfile ended in an
end of file or beginning of file condition; otherwise, it returns ’0’.
The operations that set %EOF are:
“READ (Read a Record)”
“READC (Read Next Changed Record)”
“READE (Read Equal Key)”
“READP (Read Prior Record)”
“READPE (Read Prior Equal)”
“WRITE (Create New Records)” subfile only).
The following operations, if successful, set %EOF(filename) off. If the operation is
not successful, %EOF(filename) is not changed. %EOF with no parameter is not
changed by these operations.
“CHAIN (Random Retrieval from a File)”
“OPEN (Open File for Processing)”
“SETGT (Set Greater Than)”
“SETLL (Set Lower Limit)”
When a full-procedural file is specified, this function returns ’1’ if the previous
operation in the list above, for the specified file, resulted in an end of file or
beginning of file condition. For primary and secondary files, %EOF is available
only if the file name is specified. It is set to ’1’ if the most recent input operation
during *GETIN processing resulted in an end of file or beginning of file condition.
Otherwise, it returns ’0’.
This function is allowed for input, update, and record-address files; and for display
files allowing WRITE to subfile records.
Read More...
end of file or beginning of file condition; otherwise, it returns ’0’.
The operations that set %EOF are:
“READ (Read a Record)”
“READC (Read Next Changed Record)”
“READE (Read Equal Key)”
“READP (Read Prior Record)”
“READPE (Read Prior Equal)”
“WRITE (Create New Records)” subfile only).
The following operations, if successful, set %EOF(filename) off. If the operation is
not successful, %EOF(filename) is not changed. %EOF with no parameter is not
changed by these operations.
“CHAIN (Random Retrieval from a File)”
“OPEN (Open File for Processing)”
“SETGT (Set Greater Than)”
“SETLL (Set Lower Limit)”
When a full-procedural file is specified, this function returns ’1’ if the previous
operation in the list above, for the specified file, resulted in an end of file or
beginning of file condition. For primary and secondary files, %EOF is available
only if the file name is specified. It is set to ’1’ if the most recent input operation
during *GETIN processing resulted in an end of file or beginning of file condition.
Otherwise, it returns ’0’.
This function is allowed for input, update, and record-address files; and for display
files allowing WRITE to subfile records.
Read More...
Saturday, August 15, 2009
(Tips) AS/400 Built In Functions %Equal
%EQUAL (Return Exact Match Condition)
%EQUAL returns ’1’ if the most recent relevant operation found an exact match;
otherwise, it returns ’0’.
The operations that set %EQUAL are:
“SETLL (Set Lower Limit)”
“LOOKUP (Look Up a Table or Array Element)”
If %EQUAL is used without the optional file_name parameter, then it returns the
value set for the most recent relevant operation.
For the SETLL operation, this function returns ’1’ if a record is present whose key
or relative record number is equal to the search argument.
For the LOOKUP operation with the EQ indicator specified, this function returns
’1’ if an element is found that exactly matches the search argument.
If a file name is specified, this function applies to the most recent SETLL operation
for the specified file.
Examples:
Read More...
%EQUAL returns ’1’ if the most recent relevant operation found an exact match;
otherwise, it returns ’0’.
The operations that set %EQUAL are:
“SETLL (Set Lower Limit)”
“LOOKUP (Look Up a Table or Array Element)”
If %EQUAL is used without the optional file_name parameter, then it returns the
value set for the most recent relevant operation.
For the SETLL operation, this function returns ’1’ if a record is present whose key
or relative record number is equal to the search argument.
For the LOOKUP operation with the EQ indicator specified, this function returns
’1’ if an element is found that exactly matches the search argument.
If a file name is specified, this function applies to the most recent SETLL operation
for the specified file.
Examples:
Read More...
(Tips) AS/400 Built In Functions %Found
%FOUND (Return Found Condition)
%FOUND returns ’1’ if the most recent relevant file operation found a record, a
string operation found a match, or a search operation found an element.
Otherwise, this function returns ’0’.
The operations that set %FOUND are:
File operations:
– “CHAIN (Random Retrieval from a File)”
– “DELETE (Delete Record)”
– “SETGT (Set Greater Than)”
– “SETLL (Set Lower Limit)”
String operations:
– “CHECK (Check Characters)”
– “CHECKR (Check Reverse)”
– “SCAN (Scan String)”
Note: Built-in function %SCAN does not change the value of %FOUND.
Search operations:
– “LOOKUP (Look Up a Table or Array Element)”
If %FOUND is used without the optional file_name parameter, then it returns the
value set for the most recent relevant operation. When a file_name is specified,
then it applies to the most recent relevant operation on that file.
For file operations, %FOUND is opposite in function to the ″no record found NR″
indicator.
For string operations, %FOUND is the same in function as the ″found FD″
indicator.
For the LOOKUP operation, %FOUND returns ’1’ if the operation found an
element satisfying the search conditions.
Examples:
Read More...
%FOUND returns ’1’ if the most recent relevant file operation found a record, a
string operation found a match, or a search operation found an element.
Otherwise, this function returns ’0’.
The operations that set %FOUND are:
File operations:
– “CHAIN (Random Retrieval from a File)”
– “DELETE (Delete Record)”
– “SETGT (Set Greater Than)”
– “SETLL (Set Lower Limit)”
String operations:
– “CHECK (Check Characters)”
– “CHECKR (Check Reverse)”
– “SCAN (Scan String)”
Note: Built-in function %SCAN does not change the value of %FOUND.
Search operations:
– “LOOKUP (Look Up a Table or Array Element)”
If %FOUND is used without the optional file_name parameter, then it returns the
value set for the most recent relevant operation. When a file_name is specified,
then it applies to the most recent relevant operation on that file.
For file operations, %FOUND is opposite in function to the ″no record found NR″
indicator.
For string operations, %FOUND is the same in function as the ″found FD″
indicator.
For the LOOKUP operation, %FOUND returns ’1’ if the operation found an
element satisfying the search conditions.
Examples:
Read More...
Tuesday, July 28, 2009
(Q&A) What is the Difference if I do the RESET or not?
(From : Oyie)
The CONNECT RESET statement is used to connect to a local database, you can use this if your AS/400 is not yet connected to any SQL servers. By default AS/400 is connected locally, therefore you don't need to execute CONNECT RESET in your program.
Example:
Try using DISCONNECT CURRENT to disconnect to your database, then issue CONNECT RESET to re-establish database connection.
C/EXEC SQL
C+ DISCONNECT CURRENT
C/END-EXEC
C/EXEC SQL
C+ CONNECT RESET
C/END-EXEC
Read More...
The CONNECT RESET statement is used to connect to a local database, you can use this if your AS/400 is not yet connected to any SQL servers. By default AS/400 is connected locally, therefore you don't need to execute CONNECT RESET in your program.
Example:
Try using DISCONNECT CURRENT to disconnect to your database, then issue CONNECT RESET to re-establish database connection.
C/EXEC SQL
C+ DISCONNECT CURRENT
C/END-EXEC
C/EXEC SQL
C+ CONNECT RESET
C/END-EXEC
Read More...
Monday, July 27, 2009
(Q&A) Can I connect to another AS400 file with embedded SQL in RPGLE?
(From : Anonymous)
If you are going to connect to a local database you can use CONNECT RESET SQL statement, but if you want to connect to another database/machine, you can connect to the desired machine by replacing the RESET parameter with the name of the remote database.
Read More...
If you are going to connect to a local database you can use CONNECT RESET SQL statement, but if you want to connect to another database/machine, you can connect to the desired machine by replacing the RESET parameter with the name of the remote database.
Read More...
Monday, July 20, 2009
(AS/400 For Dummies) Source Physical Files
Within the course we will be dealing with 3 kinds of files that contain members. Source Physical Files, Physical Files and Logical Files. Let's discuss first their differences. Physical files are files that contain data, Source Physical Files contain program sources, Logical Files do not contain data or program sources, it is a file that provides a view of the data stored into the Physical file.
In this chapter we will learning on how to create source physical file and on how to create source physical file members.
We need to create the following source physical files:
1) QDDSSRC - SRC-PF for Physical Files and Logical Files
2) QDSPSRC - SRC-PF for Display Files
3) QPRTSRC - SRC-PF for Printer Files
4) QRPGSRC - SRC-PF for RPG Program
5) QCLSRC - SRC-PF for CL Program
6) QRPGLESRC - SRC-PF for RPG ILE Program
7) QCLLESRC - SRC-PF for CL ILE Program
1-5 will have lengths of 92 while 6 and 7 will have 112.
Let's create QDDSSRC. On the command line type CRTSRCPF then press F4 key. Your screen should be like this:
Under the FILE parameter type QDDSSRC, then for the LIBRARY type the name of the library that you have created, in my case it's MYLIBRARY. For the LENGTH it should be 92, then for the DESCRIPTION type "PF and LF Sources". Your screen should look like this:
Then press ENTER key. You have now crreated your first SRC-PF. You can do the same method for the remaining 6 SRC-PFs, for items 6 and 7 don't forget to change the lenght to 112.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
In this chapter we will learning on how to create source physical file and on how to create source physical file members.
We need to create the following source physical files:
1) QDDSSRC - SRC-PF for Physical Files and Logical Files
2) QDSPSRC - SRC-PF for Display Files
3) QPRTSRC - SRC-PF for Printer Files
4) QRPGSRC - SRC-PF for RPG Program
5) QCLSRC - SRC-PF for CL Program
6) QRPGLESRC - SRC-PF for RPG ILE Program
7) QCLLESRC - SRC-PF for CL ILE Program
1-5 will have lengths of 92 while 6 and 7 will have 112.
Let's create QDDSSRC. On the command line type CRTSRCPF then press F4 key. Your screen should be like this:
Under the FILE parameter type QDDSSRC, then for the LIBRARY type the name of the library that you have created, in my case it's MYLIBRARY. For the LENGTH it should be 92, then for the DESCRIPTION type "PF and LF Sources". Your screen should look like this:
Then press ENTER key. You have now crreated your first SRC-PF. You can do the same method for the remaining 6 SRC-PFs, for items 6 and 7 don't forget to change the lenght to 112.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Friday, July 17, 2009
(AS/400 For Dummies) Creating Your First Library
Now that you are somewhat familiar with the AS/400 environment, let us try to create your personal library.
In the command line just type CRTLIB then press F4 key. Your screen should look like this:
We just need to supply the following parameters: Library (LIB), Library Type (TYPE) and Text Description (TEXT).
a) LIB, input the desired library name.
b) TYPE, input *TEST, since this is just a test library.
c) TEXT, input the description of your library.
See sample inputted parameter values:
After you are done setting up the parameters just press ENTER key. You now have created your first library.
You can also create the library using this command (w/o prompt screen):
CRTLIB LIB(MYLIBRARY) TYPE(*TEST) TEXT('My First Library')
You can check if the library was successfully created by using WRKOBJ command.
WRKOBJ OBJ(MyLibrary)
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
In the command line just type CRTLIB then press F4 key. Your screen should look like this:
We just need to supply the following parameters: Library (LIB), Library Type (TYPE) and Text Description (TEXT).
a) LIB, input the desired library name.
b) TYPE, input *TEST, since this is just a test library.
c) TEXT, input the description of your library.
See sample inputted parameter values:
After you are done setting up the parameters just press ENTER key. You now have created your first library.
You can also create the library using this command (w/o prompt screen):
CRTLIB LIB(MYLIBRARY) TYPE(*TEST) TEXT('My First Library')
You can check if the library was successfully created by using WRKOBJ command.
WRKOBJ OBJ(MyLibrary)
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Thursday, July 16, 2009
(AS/400 For Dummies) Programming Development Manager (PDM)
Now, let's start talking about PDM. PDM is a part of application development tools which helps us work with libraries, objects and members. With PDM we can do library, object and member manipulation using a menu type interface, this is very useful for AS/400 beginners.
Here are some of PDM functions:
1) Work with libraries.
2) Work with objects.
3) Work with members.
4) Work with user defined options.
We can start using PDM menu by typing STRPDM on the command line then press ENTER key. Your screen would look like this:
Options 1-3 can be done using the following shortcut commands.
1)WRKLIBPDM - for library manipulation.
2)WRKOBJPDM - for object manipulation.
3)WRKMBRPDM - for member manipulation.
For the 3 commands, we can notice that WRKxxxPDM is common. For "xxx" we can substitute LIB (for library), OBJ (for object) and MBR (for member).
To manipulate library/object/member you can use the top options list as your reference for desired action. Options list is located here (inside the red box):
For example, you want to copy DEMO library, you can type "3" on the option field located at the leftmost portion of the list of libraries, then press ENTER key, now you will be prompted to enter new library name, after inputting the new name you can now press ENTER again.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Here are some of PDM functions:
1) Work with libraries.
2) Work with objects.
3) Work with members.
4) Work with user defined options.
We can start using PDM menu by typing STRPDM on the command line then press ENTER key. Your screen would look like this:
Options 1-3 can be done using the following shortcut commands.
1)WRKLIBPDM - for library manipulation.
2)WRKOBJPDM - for object manipulation.
3)WRKMBRPDM - for member manipulation.
For the 3 commands, we can notice that WRKxxxPDM is common. For "xxx" we can substitute LIB (for library), OBJ (for object) and MBR (for member).
To manipulate library/object/member you can use the top options list as your reference for desired action. Options list is located here (inside the red box):
For example, you want to copy DEMO library, you can type "3" on the option field located at the leftmost portion of the list of libraries, then press ENTER key, now you will be prompted to enter new library name, after inputting the new name you can now press ENTER again.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Tuesday, July 14, 2009
(AS/400 For Dummies) AS/400 Libraries
In AS400, libraries are very important to one's AS/400 job. Each AS/400 job has it own library lists. A library list is an ordered list of library names. It identifies the libraries that a user can use within a job and it also sequentially orders library priority.
By default, user only have the following system defined libraries to work on.
QSYS
QSYS2
QHLPSYSQUSRSYS
QGPL
QTEMP
We can use ADLIBLE or EDTLIBL to add user defined libraries.
Here's a list of commands that we can use to manipulate libraries.
1) DSPLIBL - it displays current library list.
2) CHGLIBL - it is used to change user portion of the current job's library list.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
By default, user only have the following system defined libraries to work on.
QSYS
QSYS2
QHLPSYSQUSRSYS
QGPL
QTEMP
We can use ADLIBLE or EDTLIBL to add user defined libraries.
Here's a list of commands that we can use to manipulate libraries.
1) DSPLIBL - it displays current library list.
2) CHGLIBL - it is used to change user portion of the current job's library list.
3) ADDLIBLE - adds single library to user portion of the library list.
4) RMVLIBLE - removes single library from the user portion of the library list.
5) EDTLIBL - It displays current library list and it also allows the user to make changes in the user portion of the library list.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
Monday, July 13, 2009
(AS/400 For Dummies) OS/400
OS/400
- AS/400's operating system. It is designed to cater multi-user and multi programming machine. It allows simultaneous program execution and program processing. The importance of understanding this will be helpful later in the course.
OS/400 Functions:
a) Programming
-Different programming utilities are available for developers. Such utilities are as follows: Source Entry Utility (SEU), Screen Design Aid (SDA), Report Layout Utility (RLU), Data File Utility (DFU), Query Utilities, Structured Query Language (SQL), etc. These utilities are very helpful in creating programs.
b) Data Management
-OS/400 offers data file maintenance. It allows both physical files and logical files data management.
d) Spooling Functions
-The OS also comes with printing queue functions, this is very important in a multi-user environment.
e) Work Management
-It allow administrators to manage job within the system. It's also very helpful in resources allocation and maintenance.
f) Security Functions
-OS/400 contains numerous access control features that makes AS/400 a very secured machine.
Common OS/400 Object Types:
1) Libray (*LIB)
-Libraries are logical spaces that holds objects. This is the place where you place your files, programs, out queues, job queues, etc. If you will compare it to PC it's like a directory. But unlike "directories", libraries can not contain another library whithin itself.
2) File (*FILE)
-Files are the ones holding the data, either permanent or temporary data. It can be one of the following; Physical File, Logical File, Display File, Printer File, Source Physical File, etc.
3) Program (*PGM)
-Executable programs. It is the object produced after compiling you program source.
OS/400 Commands:
OS/400 command is consist of command name and parameters. Command name is the instruction on what the user wants to do. Parameters are the details of the command operation.
Command names are in a simple VERB/NOUN construction, the the verb usually contains 3 character.
Examples:
1) DLT is for Delete Function
2) CHG is for Change Function
3) RNM is for Rename Function
Noun defines the object you want to access.
Examples:
1) F is for File
2) PF is for Physical File
3) OBJ is for Object.
Let's try to issue a command using the VERB and NOUNE combination.
a) To delete a file the command name should be: DLTF
b) To transfor object from one library to another the command name should be: MOVOBJ
c) To rename an obect the command name should be: RNMOBJ
To supply parameters, simply type the command name in the command line then press "F4" key, OS/400 will prompt user for required parameters.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
- AS/400's operating system. It is designed to cater multi-user and multi programming machine. It allows simultaneous program execution and program processing. The importance of understanding this will be helpful later in the course.
OS/400 Functions:
a) Programming
-Different programming utilities are available for developers. Such utilities are as follows: Source Entry Utility (SEU), Screen Design Aid (SDA), Report Layout Utility (RLU), Data File Utility (DFU), Query Utilities, Structured Query Language (SQL), etc. These utilities are very helpful in creating programs.
b) Data Management
-OS/400 offers data file maintenance. It allows both physical files and logical files data management.
d) Spooling Functions
-The OS also comes with printing queue functions, this is very important in a multi-user environment.
e) Work Management
-It allow administrators to manage job within the system. It's also very helpful in resources allocation and maintenance.
f) Security Functions
-OS/400 contains numerous access control features that makes AS/400 a very secured machine.
Common OS/400 Object Types:
1) Libray (*LIB)
-Libraries are logical spaces that holds objects. This is the place where you place your files, programs, out queues, job queues, etc. If you will compare it to PC it's like a directory. But unlike "directories", libraries can not contain another library whithin itself.
2) File (*FILE)
-Files are the ones holding the data, either permanent or temporary data. It can be one of the following; Physical File, Logical File, Display File, Printer File, Source Physical File, etc.
3) Program (*PGM)
-Executable programs. It is the object produced after compiling you program source.
OS/400 Commands:
OS/400 command is consist of command name and parameters. Command name is the instruction on what the user wants to do. Parameters are the details of the command operation.
Command names are in a simple VERB/NOUN construction, the the verb usually contains 3 character.
Examples:
1) DLT is for Delete Function
2) CHG is for Change Function
3) RNM is for Rename Function
Noun defines the object you want to access.
Examples:
1) F is for File
2) PF is for Physical File
3) OBJ is for Object.
Let's try to issue a command using the VERB and NOUNE combination.
a) To delete a file the command name should be: DLTF
b) To transfor object from one library to another the command name should be: MOVOBJ
c) To rename an obect the command name should be: RNMOBJ
To supply parameters, simply type the command name in the command line then press "F4" key, OS/400 will prompt user for required parameters.
You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...
(Q&A) Why do we hit a session device error?
(From : Anonymous)
There are three types of AS/400 session device error:
1. Permanent device or session error on I/O operation
A major return code of 81 indicates a serious error that affects the device or session.
These errors include hardware failures that affect the device, communications line, or communications controller. It also includes errors because of a device being disconnected or powered off unexpectedly, and abnormal conditions that are discovered by the device and reported back to the system. Both the minor return code and the accompanying message provide more specific information about the cause of the problem.
Depending on the file type, the program must either close the file and open it again, release the device and acquire it again, or acquire the session again. To reset an error condition in a shared file by closing it and opening it again, all programs sharing the open data path must close the file. In some cases, the message might instruct you to reset the device by varying it off and on again. It is unlikely that the program will be able to use the failing device until the problem causing the error is found and corrected, but recovery within the program might be possible if an alternate device is available.
Some of the minor return codes in this group are the same as those for the 82 major return code. Device failures or line failures might occur at any time, but an 81 major code occurs on an I/O operation. This means that your program had already established a link with the device or session. Therefore, the program can transfer some data, but when the program starts from the beginning when it starts again. A possible duplication of data might result.
Message numbers accompanying an 81 major code might be in the range that indicates either an I/O or a close operation. A device failure on a close operation might be the result of a failure in sending the final block of data, rather than action specific to closing the file. An error on a close operation can cause a file to not close completely. Your error recovery program should respond to close failures with a second close operation. The second close will always complete, regardless of errors.
2. Device or session error on open or acquire operation
A major return code of 82 indicates that a device error or a session error occurred during an open or acquire operation. Both the minor return code and the accompanying message will provide more specific information regarding the cause of the problem.
Some of the minor return codes in this group are the same as those for the 81 major return code. Device or line failures might occur at any time, but an 82 major code indicates that the device or session was unusable when your program first attempted to use it. Thus no data was transferred. The problem might be the result of a configuration or installation error.
Depending on the minor return code, it might be appropriate for your program to recover from the error and try the failing operation again after some waiting period. You should specify the number of times you try in your program. It might also be possible to use an alternate or backup device or session instead.
Message numbers accompanying an 82 major code might be in the range that indicates either an open or acquire operation. If the operation is an open operation, it is necessary to close the partially opened file and reopen it to recover from the error. If the operation is an acquire operation, it might be necessary to do a release operation before trying the acquire operation again. In either case, specify a wait time for the file that is long enough so that the system can recover from the error.
3. Recoverable device or session errors on I/O operation
A major return code of 83 indicates that an error occurred in sending data to a device or receiving data from the device. Recovery by the application program is possible. Both the minor return code and the accompanying message provide more specific information regarding the cause of the problem.
Most of the errors in this group are the result of sending commands or data that are not valid to the device, or sending valid data at the wrong time or to a device that is not able to handle it. The application program can recover by skipping the failing operation or data item and going on to the next one, or by substituting an appropriate default. There might be a logic error in the application.
The most common one is type 2, wherein you are trying to write an empty subfile. To find out the details of the encountered error, check the session's joblog by using DSPJOBLOG command then press ENTER, F10, PAGEUP, point your cursor to the error then hit F1.
Read More...
There are three types of AS/400 session device error:
1. Permanent device or session error on I/O operation
A major return code of 81 indicates a serious error that affects the device or session.
These errors include hardware failures that affect the device, communications line, or communications controller. It also includes errors because of a device being disconnected or powered off unexpectedly, and abnormal conditions that are discovered by the device and reported back to the system. Both the minor return code and the accompanying message provide more specific information about the cause of the problem.
Depending on the file type, the program must either close the file and open it again, release the device and acquire it again, or acquire the session again. To reset an error condition in a shared file by closing it and opening it again, all programs sharing the open data path must close the file. In some cases, the message might instruct you to reset the device by varying it off and on again. It is unlikely that the program will be able to use the failing device until the problem causing the error is found and corrected, but recovery within the program might be possible if an alternate device is available.
Some of the minor return codes in this group are the same as those for the 82 major return code. Device failures or line failures might occur at any time, but an 81 major code occurs on an I/O operation. This means that your program had already established a link with the device or session. Therefore, the program can transfer some data, but when the program starts from the beginning when it starts again. A possible duplication of data might result.
Message numbers accompanying an 81 major code might be in the range that indicates either an I/O or a close operation. A device failure on a close operation might be the result of a failure in sending the final block of data, rather than action specific to closing the file. An error on a close operation can cause a file to not close completely. Your error recovery program should respond to close failures with a second close operation. The second close will always complete, regardless of errors.
2. Device or session error on open or acquire operation
A major return code of 82 indicates that a device error or a session error occurred during an open or acquire operation. Both the minor return code and the accompanying message will provide more specific information regarding the cause of the problem.
Some of the minor return codes in this group are the same as those for the 81 major return code. Device or line failures might occur at any time, but an 82 major code indicates that the device or session was unusable when your program first attempted to use it. Thus no data was transferred. The problem might be the result of a configuration or installation error.
Depending on the minor return code, it might be appropriate for your program to recover from the error and try the failing operation again after some waiting period. You should specify the number of times you try in your program. It might also be possible to use an alternate or backup device or session instead.
Message numbers accompanying an 82 major code might be in the range that indicates either an open or acquire operation. If the operation is an open operation, it is necessary to close the partially opened file and reopen it to recover from the error. If the operation is an acquire operation, it might be necessary to do a release operation before trying the acquire operation again. In either case, specify a wait time for the file that is long enough so that the system can recover from the error.
3. Recoverable device or session errors on I/O operation
A major return code of 83 indicates that an error occurred in sending data to a device or receiving data from the device. Recovery by the application program is possible. Both the minor return code and the accompanying message provide more specific information regarding the cause of the problem.
Most of the errors in this group are the result of sending commands or data that are not valid to the device, or sending valid data at the wrong time or to a device that is not able to handle it. The application program can recover by skipping the failing operation or data item and going on to the next one, or by substituting an appropriate default. There might be a logic error in the application.
The most common one is type 2, wherein you are trying to write an empty subfile. To find out the details of the encountered error, check the session's joblog by using DSPJOBLOG command then press ENTER, F10, PAGEUP, point your cursor to the error then hit F1.
Read More...
Friday, July 10, 2009
(FAQ) How To Convert Numeric To Character Format In AS/400 RPG-ILE.
We can convert numeric to character format in two ways; using BIF %CHAR and %EDITC.
1. %CHAR(expression{:format})
%CHAR converts the value of the expression from graphic, UCS-2, numeric, date,time or timestamp data to type character. The converted value remains unchanged,
but is returned in a format that is compatible with character data.
Sample:
a. To format the time and date with the default formats, use this:
result = ’It is ’ + %CHAR(time) + ’ on ’ + %CHAR(date)
b. To format the time and date with specific formats, use this:
result = ’It is ’ + %CHAR(time : *hms:) + ’ on ’ + %CHAR(date : *iso)
c. You can use %subst with the %char result if you only want part of the result:
result = ’The time is now ’ + %SUBST (%CHAR(time):1:5) + ’.’
d. Use %CHAR to convert a graphic value to character so it can be concatenated with a character value:
result = ’The customer’’s name is ’ + %CHAR(Name) + ’.’
e. Use %CHAR to convert a number to character format:
result = ’You have ’ + %char(points) + ’ points.’
2. %EDITC(numeric : editcode {: *ASTFILL | *CURSYM | currency-symbol})
This function returns a character result representing the numeric value edited
according to the edit code. In general, the rules for the numeric value and edit
code are identical to those for editing numeric values in output specifications. The
third parameter is optional, and if specified, must be one of:
a. *ASTFILL
Indicates that asterisk protection is to be used. This means that leading
zeros are replaced with asterisks in the returned value. For example,
%EDITC(-0012.5 : ’K’ : *ASTFILL) returns ’***12.5-’.
b. *CURSYM
Indicates that a floating currency symbol is to be used. The actual symbol
will be the one specified on the control specification in the CURSYM
keyword, or the default, ’$’. When *CURSYM is specified, the currency
symbol is placed in the the result just before the first significant digit. For
example, %EDITC(0012.5 : ’K’ : *CURSYM) returns ’ $12.5 ’.
c. currency-symbol
Indicates that floating currency is to be used with the provided currency
symbol. It must be a 1-byte character constant (literal, named constant or
expression that can be evaluated at compile time). For example,
%EDITC(0012.5 : ’K’ : ’X’) returns ’ X12.5 ’.
List of Edit Codes:
X – (obsolete; but it does retain leading zeros!)
Y – Slash insertion for date
Z – Zero suppresses leading zeros (no decimals, negatives)
Sample:
Read More...
1. %CHAR(expression{:format})
%CHAR converts the value of the expression from graphic, UCS-2, numeric, date,time or timestamp data to type character. The converted value remains unchanged,
but is returned in a format that is compatible with character data.
Sample:
a. To format the time and date with the default formats, use this:
result = ’It is ’ + %CHAR(time) + ’ on ’ + %CHAR(date)
b. To format the time and date with specific formats, use this:
result = ’It is ’ + %CHAR(time : *hms:) + ’ on ’ + %CHAR(date : *iso)
c. You can use %subst with the %char result if you only want part of the result:
result = ’The time is now ’ + %SUBST (%CHAR(time):1:5) + ’.’
d. Use %CHAR to convert a graphic value to character so it can be concatenated with a character value:
result = ’The customer’’s name is ’ + %CHAR(Name) + ’.’
e. Use %CHAR to convert a number to character format:
result = ’You have ’ + %char(points) + ’ points.’
2. %EDITC(numeric : editcode {: *ASTFILL | *CURSYM | currency-symbol})
This function returns a character result representing the numeric value edited
according to the edit code. In general, the rules for the numeric value and edit
code are identical to those for editing numeric values in output specifications. The
third parameter is optional, and if specified, must be one of:
a. *ASTFILL
Indicates that asterisk protection is to be used. This means that leading
zeros are replaced with asterisks in the returned value. For example,
%EDITC(-0012.5 : ’K’ : *ASTFILL) returns ’***12.5-’.
b. *CURSYM
Indicates that a floating currency symbol is to be used. The actual symbol
will be the one specified on the control specification in the CURSYM
keyword, or the default, ’$’. When *CURSYM is specified, the currency
symbol is placed in the the result just before the first significant digit. For
example, %EDITC(0012.5 : ’K’ : *CURSYM) returns ’ $12.5 ’.
c. currency-symbol
Indicates that floating currency is to be used with the provided currency
symbol. It must be a 1-byte character constant (literal, named constant or
expression that can be evaluated at compile time). For example,
%EDITC(0012.5 : ’K’ : ’X’) returns ’ X12.5 ’.
List of Edit Codes:
X – (obsolete; but it does retain leading zeros!)
Y – Slash insertion for date
Z – Zero suppresses leading zeros (no decimals, negatives)
Sample:
Read More...
Thursday, July 9, 2009
(Tips) Renaming Program Indicators AS/400 RPG
Naming your numeric indicators used in screen displays or printer files is an effective way to make your programs more readable and understandable, but did you know there are two ways it can be done? The first method is associating a data structure using the INDDS keyword, and the second method is by address association using the BASED keyword.
The first method uses the INDDS keyword which references a data structure. Within the data structure you give each indicator you use a unique name. So instead of saying IF *IN99 = *ON, you can say IF ERROR = *ON. This provides someone who has never been in the program before a better understanding of what the test is, instead of having to hunt down every occurrence of the indicator and figure it out from there.
The keyword INDDS references a data structure named Indicators. The data structure is a length of 99, but you don't need to specify every indicator, only the ones you're using. Additionally, the keyword INDARA needs to be present in the display or printer file you're referencing. The data structure can be referenced by more than one display and/or printer file within the program. You can also create separate ones for each. The second method uses the BASED keyword. It is similar to the first method except it does not require changes to the file specifications or either the display or printer files; all the changes are in the data structure, like so:
Here, the built-in function %ADDR references the Indicator array, and BASED references the data structure you provide. A difference between the two methods is that the first method can refer to a different data structure for each display and/or printer file you use, whereas the second one would apply to all. Here's an added tip, regardless of which method you use. You are not restricted to one name for each indicator. It is possible to supply multiple names for each. For example if indicator 50 is used to condition a field on two different screens, each with a different meaning, you can specify something like this within the data structure:
The first method uses the INDDS keyword which references a data structure. Within the data structure you give each indicator you use a unique name. So instead of saying IF *IN99 = *ON, you can say IF ERROR = *ON. This provides someone who has never been in the program before a better understanding of what the test is, instead of having to hunt down every occurrence of the indicator and figure it out from there.
The keyword INDDS references a data structure named Indicators. The data structure is a length of 99, but you don't need to specify every indicator, only the ones you're using. Additionally, the keyword INDARA needs to be present in the display or printer file you're referencing. The data structure can be referenced by more than one display and/or printer file within the program. You can also create separate ones for each. The second method uses the BASED keyword. It is similar to the first method except it does not require changes to the file specifications or either the display or printer files; all the changes are in the data structure, like so:
Here, the built-in function %ADDR references the Indicator array, and BASED references the data structure you provide. A difference between the two methods is that the first method can refer to a different data structure for each display and/or printer file you use, whereas the second one would apply to all. Here's an added tip, regardless of which method you use. You are not restricted to one name for each indicator. It is possible to supply multiple names for each. For example if indicator 50 is used to condition a field on two different screens, each with a different meaning, you can specify something like this within the data structure:
This way you're not stuck using a name in another part of the program that isn't the intended meaning. A word of caution however, as when you condition one of the named indicators above, it affects both when they're sharing the same indicator within a data structure. As long as this is kept in mind when designing the program, you'll be OK.
Wednesday, July 8, 2009
(iSeries Blunders) Part III
December 14th, 2005 by ReiVi
Source : http://techblog.livingatrandom.com
There was this one time that I was at a client site doing some programming work. I noticed that one of the system operators was removing and then re-inserting a cartridge from the AS/400’s tape drive. He would walk back to the main console and type something. Shaking his head he would go through the entire process again.
After a few more minutes, I heard him mutter what sounded like a mixture of a curse and thank you.
I couldn’t take it anymore, so I asked him what happened.
Apparently, he was trying to initialize the tape in preparation for a backup. Whenever he typed the INZTAP command on the console, it would return an error that there was no tape in the drive. He removed and re-inserted the tape. He even tried using brand new tapes to no avail. Finally, he decided to signoff of the AS/400. Just before signing back-in, he noticed that he was connected to the remote AS/400 all that time.
He was very thankful that there was no tape on the remote (production environment) AS/400. If there’d been one — and it contained important data — he would have unknowingly erased it’s contents.
Lesson learned: If you work for a company that has multiple AS/400’s — whether local or remote — make sure that you are connected to the correct machine before doing anything.
Read More...
Source : http://techblog.livingatrandom.com
There was this one time that I was at a client site doing some programming work. I noticed that one of the system operators was removing and then re-inserting a cartridge from the AS/400’s tape drive. He would walk back to the main console and type something. Shaking his head he would go through the entire process again.
After a few more minutes, I heard him mutter what sounded like a mixture of a curse and thank you.
I couldn’t take it anymore, so I asked him what happened.
Apparently, he was trying to initialize the tape in preparation for a backup. Whenever he typed the INZTAP command on the console, it would return an error that there was no tape in the drive. He removed and re-inserted the tape. He even tried using brand new tapes to no avail. Finally, he decided to signoff of the AS/400. Just before signing back-in, he noticed that he was connected to the remote AS/400 all that time.
He was very thankful that there was no tape on the remote (production environment) AS/400. If there’d been one — and it contained important data — he would have unknowingly erased it’s contents.
Lesson learned: If you work for a company that has multiple AS/400’s — whether local or remote — make sure that you are connected to the correct machine before doing anything.
Read More...
(iSeries Blunders) Part II
August 22nd, 2005 by ReiVi
Source : http://techblog.livingatrandom.com
Have you ever worked on two programs at the same time? You are coding this current requirement when a user comes up to you from behind and blurts out, “Can you fix this? We just need an additional column on the report. My boss needed it yesterday.” Sound familiar? Knowing that it won’t take you an hour to do the modification, you agree.
Now, the thing here is, you are using a dumb terminal. Since you don’t want to exit your current task and mess up all the library list settings and stuff, you decide to use the dumb terminal next to it. You open the code up in SEU and start coding.
All of a sudden, you remembered the user never gave you the change request form. Your boss gave everyone a briefing on the subject just the other day. Setting, the keyboard aside, you call the user and request for the necessary paperwork.
While waiting for the change request to arrive, you decide to go back to your original task. Reaching for the keyboard, you start to type. But nothing happens. You hit the reset key. Still nothing. In desperation you start hitting random keys with your fingers like you are touch typist gone mad! Nothing’s happening.
“Oh no! I haven’t even saved my work yet! That’s almost a half-day’s work!”, you murmur to yourself.
As you hang your head, you glance at the other terminal and notice that the screen is garbled. What used to be code is now a jumbled mess of letters and numbers!
“What happened?”, you ask yourself in horror.
Tracing the cable of the keyboard you are using, you realize that you were using the keyboard for the other terminal!
“This has got to be the worse day in my life. Now I messed up two program codes!”
With a prayer you press F5 to refresh the screen.
The jumbled screen refreshes and you see your code, you say a short prayer thanking the heavens for your good fortune. Just then the user arrives with the change request.
Read More...
Source : http://techblog.livingatrandom.com
Have you ever worked on two programs at the same time? You are coding this current requirement when a user comes up to you from behind and blurts out, “Can you fix this? We just need an additional column on the report. My boss needed it yesterday.” Sound familiar? Knowing that it won’t take you an hour to do the modification, you agree.
Now, the thing here is, you are using a dumb terminal. Since you don’t want to exit your current task and mess up all the library list settings and stuff, you decide to use the dumb terminal next to it. You open the code up in SEU and start coding.
All of a sudden, you remembered the user never gave you the change request form. Your boss gave everyone a briefing on the subject just the other day. Setting, the keyboard aside, you call the user and request for the necessary paperwork.
While waiting for the change request to arrive, you decide to go back to your original task. Reaching for the keyboard, you start to type. But nothing happens. You hit the reset key. Still nothing. In desperation you start hitting random keys with your fingers like you are touch typist gone mad! Nothing’s happening.
“Oh no! I haven’t even saved my work yet! That’s almost a half-day’s work!”, you murmur to yourself.
As you hang your head, you glance at the other terminal and notice that the screen is garbled. What used to be code is now a jumbled mess of letters and numbers!
“What happened?”, you ask yourself in horror.
Tracing the cable of the keyboard you are using, you realize that you were using the keyboard for the other terminal!
“This has got to be the worse day in my life. Now I messed up two program codes!”
With a prayer you press F5 to refresh the screen.
The jumbled screen refreshes and you see your code, you say a short prayer thanking the heavens for your good fortune. Just then the user arrives with the change request.
Read More...
(iSeries Blunders) Part I
August 20th, 2005 by ReiVi
Source : http://techblog.livingatrandom.com
Since this is my first article, I decided to look back at my career. Starting out in 1996 with a small IT company, I was a fresh graduate and like other graduates, I had no idea what an AS/400 (which is what they called the iSeries back then) was. Naturally, I was pretty excited. When I say this big refrigerator in the middle of the room, I said to myself, “Cool! I get to program using a mainframe!” The other trainees and I were told later that day however, that it was a midrange.
“Huh? A mid-what?”
Since our exposure in college was focused on PC’s and DOS (Yeah, yeah, yeah. It was that long ago), we only had a rough idea of what other platforms were. Heck, we thought mainframes belonged to musuems even then. The dumb terminals that were assigned to us were really big…about the size of a microwave oven.
Eventually, the other trainees and I went through…well…training. We were all coding happily one day when one of us suddenly had an endless loop in a piece of code. No problem! Just press Ctrl-C or Ctrl-Break. But how? There isn’t even a Ctrl key!
Since it was almost time to go home anyway, we all suggested to our colleague, “Just turn the terminal off and we’ll go home. You can find the bug tomorrow.”
Imagine our suprise when we were all called down to our boss’ office the following day. We found out the hard way that turning the terminal off doesn’t necessarily end the job. We all were given a long lecture on how to use the System-Request key.
Read More...
Source : http://techblog.livingatrandom.com
Since this is my first article, I decided to look back at my career. Starting out in 1996 with a small IT company, I was a fresh graduate and like other graduates, I had no idea what an AS/400 (which is what they called the iSeries back then) was. Naturally, I was pretty excited. When I say this big refrigerator in the middle of the room, I said to myself, “Cool! I get to program using a mainframe!” The other trainees and I were told later that day however, that it was a midrange.
“Huh? A mid-what?”
Since our exposure in college was focused on PC’s and DOS (Yeah, yeah, yeah. It was that long ago), we only had a rough idea of what other platforms were. Heck, we thought mainframes belonged to musuems even then. The dumb terminals that were assigned to us were really big…about the size of a microwave oven.
Eventually, the other trainees and I went through…well…training. We were all coding happily one day when one of us suddenly had an endless loop in a piece of code. No problem! Just press Ctrl-C or Ctrl-Break. But how? There isn’t even a Ctrl key!
Since it was almost time to go home anyway, we all suggested to our colleague, “Just turn the terminal off and we’ll go home. You can find the bug tomorrow.”
Imagine our suprise when we were all called down to our boss’ office the following day. We found out the hard way that turning the terminal off doesn’t necessarily end the job. We all were given a long lecture on how to use the System-Request key.
Read More...
(Tips) Uses of OVERLAY Op-Code AS/400 RPG ILE
The OVERLAY keyword overlays the storage of one subfield with that of another
subfield, or with that of the data structure itself. This keyword is allowed only for
data structure subfields.
Usage:
OVERLAY(name{:pos | *NEXT})
The Name-entry subfield overlays the storage specified by the name parameter at
the position specified by the pos parameter. If pos is not specified, it defaults to 1.
Examples:
A. Storage Allocation of Subfields with Keywords DIM and OVERLAY
B. Storage Allocation of Subfields with Keywords DIM and OVERLAY
C. Defining Subfield Overlay Positions with *NEXT
Read More...
subfield, or with that of the data structure itself. This keyword is allowed only for
data structure subfields.
Usage:
OVERLAY(name{:pos | *NEXT})
The Name-entry subfield overlays the storage specified by the name parameter at
the position specified by the pos parameter. If pos is not specified, it defaults to 1.
Examples:
A. Storage Allocation of Subfields with Keywords DIM and OVERLAY
B. Storage Allocation of Subfields with Keywords DIM and OVERLAY
C. Defining Subfield Overlay Positions with *NEXT
Read More...
Tuesday, July 7, 2009
(Tips) Coding AS/400 RPG ILE Arrays
Here are samples on how to code arrays in RPG.
Definition Specifications for Different Types of Arrays:
Read More...
Definition Specifications for Different Types of Arrays:
Read More...
(Tips) Useful AS/400 RLU Commands
The following line commands are available in RLU, in addition to most SEU line commands:
DR - Define Record Format
CL - Change Line Type
SD - Create Sample Data
VF - View Field Line
NP - New Page
DC - Define Constants
DF - Define Fields
CF - Center Fields
SP - Space Fields Evenly
Read More...
DR - Define Record Format
CL - Change Line Type
SD - Create Sample Data
VF - View Field Line
NP - New Page
DC - Define Constants
DF - Define Fields
CF - Center Fields
SP - Space Fields Evenly
Read More...
Monday, July 6, 2009
(FAQ) How To Know Which AS/400 RPG Programs Are Using Specific Objects
It is very important for us to know which programs are using specific objects. For example you have updated a file, added/deleted some fields or changed field lengths. We need to know which programs need to be recompiled. We can use DSPPGMREF (Display Program Reference) command to create a file that maps all objects to all programs.
Command:
DSPPGMREF PGM(*ALL) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(MYLIB/MYFILE)
Now you have created a file MYFILE in library MYLIB that contains list of programs with objects referenced to them.
To know which program uses MYPF file we can use SQL to retrieve it.
Command:
STRSQL
SELECT * FORM MYLIB/MYFILE
WHERE WHFNAM = 'MYPF'
Note:
WHFNAM is the object referencing to the program.
Read More...
Command:
DSPPGMREF PGM(*ALL) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(MYLIB/MYFILE)
Now you have created a file MYFILE in library MYLIB that contains list of programs with objects referenced to them.
To know which program uses MYPF file we can use SQL to retrieve it.
Command:
STRSQL
SELECT * FORM MYLIB/MYFILE
WHERE WHFNAM = 'MYPF'
Note:
WHFNAM is the object referencing to the program.
Read More...
(FAQ) AS/400 Journal Restrictions
The Create Journal (CRTJRN) command creates a journal as a local journal with the specified attributes, and attaches the specified journal receiver to the journal. Once a journal is created, object changes can be journaled to it or user entries can be sent to it. The journal state of the created journal is *ACTIVE.
Restrictions:
• A journal cannot be created in the library QTEMP.
• The receiver specified must be created before issuing this command and it must be empty (that is, the receiver must not have been previously attached to a journal or have been in the process of being attached to a journal).
• This command cannot be used to create a remote journal. See the ADDRMTJRN (Add Remote Journal) command description or the Add Remote Journal (QjoAddRemoteJournal) API in the System API Reference information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.
• If RCVSIZOPT(*MAXOPT1 or *MAXOPT2 or *MAXOPT3) is not to be in effect for the journal, the maximum threshold value that can be specified for any journal receiver being attached is 1,919,999 kilobytes.
• If the library to contain the journal is on an independent ASP then the journal receiver specified must be located on an independent ASP that is in the same ASP group as the journal's library. Likewise, if the library to contain the journal is not on an independent ASP, then the journal receiver specified cannot be located on an independent ASP.
• If the library to contain the journal is on an independent ASP then ASP(*LIBASP) must be specified.
• RCVSIZOPT(*MINFIXLEN) and FIXLENDTA cannot be used for the system security audit journal QSYS/QAUDJRN. Journal entries in the security audit journal are required to contain all possible data that could be used for auditing purposes.
Read More...
Restrictions:
• A journal cannot be created in the library QTEMP.
• The receiver specified must be created before issuing this command and it must be empty (that is, the receiver must not have been previously attached to a journal or have been in the process of being attached to a journal).
• This command cannot be used to create a remote journal. See the ADDRMTJRN (Add Remote Journal) command description or the Add Remote Journal (QjoAddRemoteJournal) API in the System API Reference information in the iSeries Information Center at http://www.ibm.com/eserver/iseries/infocenter.
• If RCVSIZOPT(*MAXOPT1 or *MAXOPT2 or *MAXOPT3) is not to be in effect for the journal, the maximum threshold value that can be specified for any journal receiver being attached is 1,919,999 kilobytes.
• If the library to contain the journal is on an independent ASP then the journal receiver specified must be located on an independent ASP that is in the same ASP group as the journal's library. Likewise, if the library to contain the journal is not on an independent ASP, then the journal receiver specified cannot be located on an independent ASP.
• If the library to contain the journal is on an independent ASP then ASP(*LIBASP) must be specified.
• RCVSIZOPT(*MINFIXLEN) and FIXLENDTA cannot be used for the system security audit journal QSYS/QAUDJRN. Journal entries in the security audit journal are required to contain all possible data that could be used for auditing purposes.
Read More...
(FAQ) How To Use Commitment Control In AS/400
With Commitment Control, you ensure one of two outcomes for the file operations: either all of the file operations are successful or none of the file operations has any effect. In this way, you process a group of operations as a unit.
To use Commitment Control, you do the following:
• Use the CL commands CRTJRN (Create Journal), CRTJRNRCV (Create Journal Receiver) and STRJRNPF (Journal Physical File) to prepare for using commitment control, and the CL commands STRCMTCTL (Start Commitment Control) and ENDCMTCTL (End Commitment Control) to notify the system when you want to start and end commitment control. See the CL Reference for information on these commands.
• Specify commitment control on the file-description specifications of the files you want under commitment control.
• Use the COMMIT operation code to apply a group of changes to files under commitment control, or use the ROLLBACK operation code to eliminate the pending group of changes to files under commitment control.
Starting and Ending Commitment Control
The CL command STRCMTCTL notifies the system that you want to process files under commitment control.
The LCKLVL (Lock Level) parameter allows you to select the level at which records are locked under commitment control.
When you complete a group of changes with a COMMIT operation, you can specify a label to identify the end of the group. In the event of an abnormal job end, this identification label is written to a file, message queue, or data area so that you know which group of changes is the last group to be completed successfully. You specify this file, message queue, or data area on the STRCMTCTL command.
Before you call any program that processes files specified for commitment control, issue the STRCMTCTL command. If you call a program that opens a file specified for commitment control before you issue the STRCMTCTL command, the opening of the file will fail.
The CL command ENDCMTCTL notifies the system that your routing step has finished processing files under commitment control.
Specifying Files for Commitment Control
When a program specifies commitment control for a file, the specification applies only to the input and output operations made by this program for this file. Commitment control does not apply to operations other than input and output operations. It does not apply to files that do not have commitment control specified in the program doing the input or output operation.
When more than one program accesses a file as a shared file, all or none of the programs must specify the file to be under commitment control.
Commitment Control Operations
The COMMIT operation tells the system that you have completed a group of changes to the files under commitment control. The ROLLBACK operation eliminates the current group of changes to the files under commitment control.
If the system fails, it implicitly issues a ROLLBACK operation. You can check the identity of the last successfully completed group of changes using the label you specify in F1 of the COMMIT operation code, and the notify-object you specify on the STRCMTCTL command.
At the end of a routing step, or when you issue the ENDCMTCTL command, the OS/400 system issues an implicit ROLLBACK, which eliminates any changes since the last ROLLBACK or COMMIT operation that you issued. To ensure that all your file operations have effect, issue a COMMIT operation before ending a routing step operating under commitment control.
The OPEN operation permits input and output operations to be made to a file and the CLOSE operation stops input and output operations from being made to a file. However, the OPEN and CLOSE operations do not affect the COMMIT and ROLLBACK operations. A COMMIT or ROLLBACK operation affects a file, even after the file has been closed. For example, your program may include the following steps:
1. Issue COMMIT (for files already opened under commitment control).
2. Open a file specified for commitment control.
3. Perform some input and output operations to this file.
4. Close the file.
5. Issue ROLLBACK.
The changes made at step 3 are rolled back by the ROLLBACK operation at step 5, even though the file has been closed at step 4. The ROLLBACK operation could be issued from another program in the same routing step.
A program does not have to operate all its files under commitment control, and to do so may adversely affect performance. The COMMIT and ROLLBACK operations have no effect on files that are not under commitment control.
When multiple devices are attached to an application program, and commitment control is in effect for the files this program uses, the COMMIT or ROLLBACK operations continue to work on a file basis and not by device. The database may be updated with partially completed COMMIT blocks or changes that other users have completed may be eliminated. It is your responsibility to ensure this does not happen.
Commitment Control Locks
On the STRCMTCTL command, you specify a level of locking, either LCKLVL (*ALL) or LCKLVL (*CHG). When your program is operating under commitment control and has processed an input or output operation on a record in a file under commitment control, the record is locked by commitment control as follows:
• Your program can access the record.
• Another program in your routing step, with this file under commitment control, can read the record. If the file is a shared file, the second program can also update the record.
• Another program in your routing step that does not have this file under commitment control cannot read or update the record.
• Another program in a separate routing step, with this file under commitment control, can read the record if you specified LCKLVL (*CHG), but it cannot read the record if you specified LCKLVL (*ALL). With either lock level, the next program cannot update the record.
• Another program that does not have this file under commitment control and that is not in your routing step can read but not update the record.
• Commitment control locks are different than normal locks, depend on the LCKLVL specified, and can only be released by the COMMIT and ROLLBACK operations.
• The COMMIT and ROLLBACK operations release the locks on the records. The UNLCK operation will not release records locked using commitment control. See the CL Reference for details on lock levels.
• The number of entries that can be locked under commitment control before the COMMIT or ROLLBACK operations are required may be limited.
Commitment Control in the Program Cycle
Commitment control is intended for full procedural files, where the input and output is under your control. Do not use commitment control with primary and secondary files, where input and output is under the control of the RPG/400 program cycle. The following are some of the reasons for this recommendation:
• You cannot issue a COMMIT operation for the last total output in your program.
• It is difficult to program within the cycle for recovery from a locked-record condition.
• Level indicators are not reset by the ROLLBACK operation.
• After a ROLLBACK operation, processing matching records may produce a sequence error.
Read More...
To use Commitment Control, you do the following:
• Use the CL commands CRTJRN (Create Journal), CRTJRNRCV (Create Journal Receiver) and STRJRNPF (Journal Physical File) to prepare for using commitment control, and the CL commands STRCMTCTL (Start Commitment Control) and ENDCMTCTL (End Commitment Control) to notify the system when you want to start and end commitment control. See the CL Reference for information on these commands.
• Specify commitment control on the file-description specifications of the files you want under commitment control.
• Use the COMMIT operation code to apply a group of changes to files under commitment control, or use the ROLLBACK operation code to eliminate the pending group of changes to files under commitment control.
Starting and Ending Commitment Control
The CL command STRCMTCTL notifies the system that you want to process files under commitment control.
The LCKLVL (Lock Level) parameter allows you to select the level at which records are locked under commitment control.
When you complete a group of changes with a COMMIT operation, you can specify a label to identify the end of the group. In the event of an abnormal job end, this identification label is written to a file, message queue, or data area so that you know which group of changes is the last group to be completed successfully. You specify this file, message queue, or data area on the STRCMTCTL command.
Before you call any program that processes files specified for commitment control, issue the STRCMTCTL command. If you call a program that opens a file specified for commitment control before you issue the STRCMTCTL command, the opening of the file will fail.
The CL command ENDCMTCTL notifies the system that your routing step has finished processing files under commitment control.
Specifying Files for Commitment Control
When a program specifies commitment control for a file, the specification applies only to the input and output operations made by this program for this file. Commitment control does not apply to operations other than input and output operations. It does not apply to files that do not have commitment control specified in the program doing the input or output operation.
When more than one program accesses a file as a shared file, all or none of the programs must specify the file to be under commitment control.
Commitment Control Operations
The COMMIT operation tells the system that you have completed a group of changes to the files under commitment control. The ROLLBACK operation eliminates the current group of changes to the files under commitment control.
If the system fails, it implicitly issues a ROLLBACK operation. You can check the identity of the last successfully completed group of changes using the label you specify in F1 of the COMMIT operation code, and the notify-object you specify on the STRCMTCTL command.
At the end of a routing step, or when you issue the ENDCMTCTL command, the OS/400 system issues an implicit ROLLBACK, which eliminates any changes since the last ROLLBACK or COMMIT operation that you issued. To ensure that all your file operations have effect, issue a COMMIT operation before ending a routing step operating under commitment control.
The OPEN operation permits input and output operations to be made to a file and the CLOSE operation stops input and output operations from being made to a file. However, the OPEN and CLOSE operations do not affect the COMMIT and ROLLBACK operations. A COMMIT or ROLLBACK operation affects a file, even after the file has been closed. For example, your program may include the following steps:
1. Issue COMMIT (for files already opened under commitment control).
2. Open a file specified for commitment control.
3. Perform some input and output operations to this file.
4. Close the file.
5. Issue ROLLBACK.
The changes made at step 3 are rolled back by the ROLLBACK operation at step 5, even though the file has been closed at step 4. The ROLLBACK operation could be issued from another program in the same routing step.
A program does not have to operate all its files under commitment control, and to do so may adversely affect performance. The COMMIT and ROLLBACK operations have no effect on files that are not under commitment control.
When multiple devices are attached to an application program, and commitment control is in effect for the files this program uses, the COMMIT or ROLLBACK operations continue to work on a file basis and not by device. The database may be updated with partially completed COMMIT blocks or changes that other users have completed may be eliminated. It is your responsibility to ensure this does not happen.
Commitment Control Locks
On the STRCMTCTL command, you specify a level of locking, either LCKLVL (*ALL) or LCKLVL (*CHG). When your program is operating under commitment control and has processed an input or output operation on a record in a file under commitment control, the record is locked by commitment control as follows:
• Your program can access the record.
• Another program in your routing step, with this file under commitment control, can read the record. If the file is a shared file, the second program can also update the record.
• Another program in your routing step that does not have this file under commitment control cannot read or update the record.
• Another program in a separate routing step, with this file under commitment control, can read the record if you specified LCKLVL (*CHG), but it cannot read the record if you specified LCKLVL (*ALL). With either lock level, the next program cannot update the record.
• Another program that does not have this file under commitment control and that is not in your routing step can read but not update the record.
• Commitment control locks are different than normal locks, depend on the LCKLVL specified, and can only be released by the COMMIT and ROLLBACK operations.
• The COMMIT and ROLLBACK operations release the locks on the records. The UNLCK operation will not release records locked using commitment control. See the CL Reference for details on lock levels.
• The number of entries that can be locked under commitment control before the COMMIT or ROLLBACK operations are required may be limited.
Commitment Control in the Program Cycle
Commitment control is intended for full procedural files, where the input and output is under your control. Do not use commitment control with primary and secondary files, where input and output is under the control of the RPG/400 program cycle. The following are some of the reasons for this recommendation:
• You cannot issue a COMMIT operation for the last total output in your program.
• It is difficult to program within the cycle for recovery from a locked-record condition.
• Level indicators are not reset by the ROLLBACK operation.
• After a ROLLBACK operation, processing matching records may produce a sequence error.
Read More...
Subscribe to:
Posts (Atom)
