Month: September 2011

Delete Duplicate Records in File Before you Assign Keys using SQL

Let’s say you are building a file from multiple spreadsheets and need to consolidate and remove duplicates.  Build your DB2 file without keys load all the spread sheets with *add not *replace then run this SQL using the where statement to match the fields

Delete from ksfcexg F1 where RRN(f1) > (select MIN(RRN(F2)) from

ksfcexg F2 where F2.stcuno = F1.stcuno)

F1 and F2 are the same file

Using .CSV files on iseries / as400 DB2

In order to convert a .CSV file in the IFS to an AS/400 (DB2) use the ‘Copy from Import File’ command:

 

CPYFRMIMPF     FROMSTMF(‘Full path and file name with extension’)

Note:    You may need to use WRKLNK (Work with object Link) command to find the path filename and extension.

                                                TOFILE(Library/Filename)     

Note:     *LIBL is assumed by the system.

                                                MBROPT(*REPLACE)                                 

Note:     Valid values *ADD  *REPLACE  *UPDADD

                                                RCDDLM(*CRLF)                    

Note:     Carriage Return Line Feed works for .CSV files. There are other possible values.              

                                                FROMRCD(2)                                       

Note:     This parameter allows you to bypass headers. It may be set to any number.

                                                RPLNULLVAL(*FLDDFT)                             

Note:    This parameter allows you to replace null values with field default values. It should always be used when converting .CSV into DB2 files.

 

In order to convert an AS/400 (DB2) to a .CSV file in the IFS use the ‘Copy to Import File’ command:

 

CPYTOIMPF        FROMFILE(Library/Filename)     

Note:     *LIBL is assumed by the system.                         

TOSTMF(‘Full path and file name with extension’)

Note:     You may need to use WRKLNK (Work with object Link) command to find the path filename and extension.

MBROPT(*ADD)

Note:     Valid values       *ADD  *REPLACE

RCDDLM(*CRLF)

Note:     Carriage Return Line Feed works for .CSV files. There are other possible values.

DTAFMT(*DLM)

Note:    Because we intend to separate each field with a ‘;’ this parameter setting is required. There are other possible values.

STRDLM(*NONE)

RMVBLANK(*TRAILING)

Note:     To prevent transfer of non-significant blanks.

FLDDLM(‘;’)          

Note:    This the delimiter to ‘;’ rather than the default :,: this was a requirement from K + S for SAP data transfers.

LANSA Green Screen Print I/O Errors Subroutine

SUBROUTINE NAME(PRT_ERROR)

DEFINE     FIELD(#ERRTXT) TYPE(*CHAR) LENGTH(100) LABEL(‘Error :’)

DEFINE     FIELD(#RETCODE) TYPE(*CHAR) LENGTH(2)

DEF_LINE   NAME(#ERROR) FIELDS((#ERRTXT)) IDENTIFY(*LABEL)

USE        BUILTIN(GET_MESSAGE) TO_GET(#RETCODE #ERRTXT)

DOWHILE    COND(‘#RETCODE *EQ OK’)

PRINT      LINE(#ERROR)

USE        BUILTIN(GET_MESSAGE) TO_GET(#RETCODE #ERRTXT)

ENDWHILE

ENDROUTINE

Template Special Variables

 

Application templates may contain special variables. Special variables are predefined template variables whose values are assigned by LANSA or by template commands. These variables are used by template commands and can be substituted into RDML when the template executes. In this respect, special variables are similar to a LANSA system variable where the value of the variable is set by executing a program (a template command).

Special variables are used in both application templates commands and RDML commands. For example:

@@QUESTION PROMPT(‘Actual test condition…’)

ANSWER(@@CANS002) HELPIDS(HELP010)

IF COND(‘@@CANS002’)

ENDIF

The user is requested to enter a description of the test condition to be used in an RDML command. The user’s reply is stored in a special variable @@CANS002. The value of @@CANS002 will be substituted into the RDML IF command.

There are four basic groups of special variables:

·         general variables

·         question and answer variables

·         file variables

·         list variables.

Special variables play a very important role in templates as they provide a means of accessing and manipulating information from the template commands.

LANSA Template Commands

The LANSA template language is a simple “tag” language that relies on branch type logic. It is not a sophisticated programming language. All commands conform to the normal LANSA command conventions. For instance, positional or keyword parameter specification can be used, lists must be enclosed in brackets, etc. The template language is not compiled. It is an interpretive language.

Note: In the lists below, ‘*’ indicates user input requested.

List Commands

@@CLR_LST

Clear/define a work list

@@MAK_LSTS

Make a list of selected fields from another list(s)*

@@MRG_LSTS

Merge a list(s) with another list(s)

File Commands

@@GET_FILS

Get the files to be used in this template*

@@RTV_FLDS

Retrieve the fields in a file

@@RTV_KEYS

Retrieve the keys of a file

@@RTV_RELN

Retrieve the relationship between two files

Control Command

@@GOTO

Pass control to a label

@@IF

Test a condition and pass control to a label

@@LABEL

A label which is the subject of another command

@@CMP_IDX

Compare an index value

@@DEC_IDX

Decrement an index value

@@INC_IDX

Increment an index value

@@SET_IDX

Set an index value

Other Commands

@@COMMENT

Add a comment line to the generated RDML code

@@QUESTION

Ask a question and receive a reply*