Ramco VirtualWorks

1. Ramco VirtualWorks Coding Guidelines

2. Agenda • Why Coding Guidelines • Advantages of following standards • Various Coding Guidelines

3. Why Coding Guidelines • Coding guidelines are created to assist programmers to carry out efficient coding and avoid rework. Most of the Guidelines formulated below are based on the experience gained while executing projects and they fulfill coding standards

4. Advantages of following standards • Assist programmers to do effective coding • Avoids rework • Helps in overcoming most of frequently occurring problems • Ensures uniformity in the coding style • Ensures easy comprehension of code so that any programmer can maintain it • Ensures quality of the code produced.

5. Various Coding Guidelines • SP Naming Conventions • General SP Coding Guidelines • General Coding Guidelines with respect to V5R2 • Effective Coding guidelines in V5R2

6. SP Naming Conventions • Input Parameters should be suffixed with _in • Output Parameters should be suffixed with _out • In-Out Parameters should be suffixed with _in • Temporary Variables should be suffixed with _tmp • UDDs should be prefixed with udd_

7. General SP Coding Guidelines • Header Information should be specified for all the SPs Template for the Header Information

8. General SP Coding Guidelines contd… • Do not use native data types in any of the SPs except for M_ErrorId. Instead Udd’s should be used • Use proper naming conventions for variables • Trims and replacements on the input variables should be carried out only once at the beginning of the SP • To enhance the readability of the code a single-line spacing can be entered between one block of code and another and after the comment and the block. • Avoid using the NOT IN and DISTINCT cursors, to the extent possible.

9. General SP Coding Guidelines contd… • Avoid using Built-In / User Defined Functions in the WHERE clause except <UDD_Name>_ND() and <UDD_Name>() used for casting • Do not use any computation in the WHERE clause wherever possible. Calculate the value and store this in a variable which can be used in the WHERE clause • Do not use more than four tables in Joins • When referring to other Components tables , use Views do not use them directly • If a set of logic should be implemented in more than one place, do not duplicate the code, instead use a common sp and call it from two places.

10. General SP Coding Guidelines contd… • Main Common SP’s are recommended for Submit, Authorize, Tran and tasks like Delete • Header Validation • Detail Validation • Header Save • Detail Save • Posting data to integration services

11. General SP Coding Guidelines contd… • Functions should not be used in left hand side of Join Conditions except for <UDD_Name>_ND() and <UDD_Name>() • If multiple information should be fetched from a single table, then try to fetch everything in a single query instead of querying the same table multiple times • Proper comments have to be given before each block of statements. Brname can be referred where ever it is applicable • Do not fetch records based on the descriptions.

12. General SP Coding Guidelines contd… • Statements to be aligned as follows • Select Statement • BEGIN • SELECT<Col1> • INTO <Var> • FROM <Tbl> • WHERE<Cond1> =<Cond2> • AND <Cond2>=<Cond3>; • END; • IF Statement • IF <Condn1> THEN • <Stmts>; • END IF;

13. General SP Coding Guidelines contd… • Conditions in the WHERE clause should be sequential. The WHERE clause condition should be as per the index order of the column(/Key columns). This is a mandatory step to be followed.

14. Clause Operator Comparison Cast function Left Right If/Where All operator except like Constant Udd_name() Nil Yes Where Like Any Udd_name_ND() Yes Yes General Coding Guidelines with respect to V5R2 • Built in functions and LIKE (in both sides) expects only native data type in V5R2. For that we need to use the function <UDD_Name>_ND() which will convert the UDDs to Native Data Types. The following table gives the discipline to be followed using various clauses

15. General Coding Guidelines with respect to V5R2 contd… • All System functions (ex LIKE, TRIM etc) expects Native Datatype. So the parameters should be converted to native data type • Declare variable_tmp Udd_item_code • set variable_tmp = Trim(Udd_item_code_ND(variable_tmp)) Note: Udd_name_ND () are user defined function. Therefore, for all UDD’s that are defined you need to create relative Udd_name_ND() function

16. General Coding Guidelines with respect to V5R2contd… • UDD Casting has to be done using <UDD_Name>() for constant values in V5R2 in the IF and WHERE clause. • All constant and literals can be compared as shown below instead of using Cast function . • -- Sample UDD Creation • CREATE DISTINCT TYPE UDD_TEST AS INTEGER WITH COMPARISONS; • -- Sample table • CREATE TABLE UDDTESTTABLE ( • REGION CHAR(5), • YEAR UDD_TEST) • -- Sample Data • insert into UDDTESTTABLE • (REGION,YEAR) • values('HYD',2003) • -- Casting Constants using system generated UDD functions • select * from UDDTESTTABLE • where year > udd_test(2002);

17. General Coding Guidelines with respect to V5R2contd… • Ensure that all the cursor declarations have only WITH RETURN instead of having WITH RETURN TO CLIENT • The terminator of the procedure must be succeeded by semicolon • The Result sets should not have double quotes • The “Dynamic Result sets 1” statement must be incorporated with all the procedures before to BEGIN to express the results from the back end • Avoid using OrA8 functions if there are relevant DB2 native functions already available. Otherwise new User defined functions must be written in DB2.

18. General Coding Guidelines with respect to V5R2contd… • Avoid using Upper, Lower functions unless it is required • Before testing through FE screens, test the SP's in BE by providing the parameter values. • “Type” is a DB2 keyword so avoid declaring variables by the name Type. • SP parameter or variable type should be equal to Udd type of the column name. This is required during comparison or updation with respect to table. • Avoid using NoPad() function.

19. Exception Handling • In db2 the exceptions are handled effectively by exception handlers. • The following is the syntax for handler declaration: DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR <condition> SQL-procedure-statement; where <condition> is one of the following: _ SQLSTATE value _ SQLEXEPTION (SQLCODE < 0) _ SQLWARNING (SQLCODE > 0) _ NOT FOUND _ Condition name

20. Exception Handling contd… Example 1 If the updation of the table books has to happen when the sql state is ‘22001’ and the author is 'JACK LONDON' with the first 50 characters of the already existing description. Then • DECLARE SQLCODE INTEGER DEFAULT 0; • DECLARE SQLSTATE CHAR(5) DEFAULT ' '; • DECLARE v_trunc INT DEFAULT 0; • DECLARE value_error CONDITION FOR SQLSTATE '22001'; • DECLARE CONTINUE HANDLER FOR value_error • BEGIN • UPDATE books • SET title_desc = substr(in_title_desc,1,50) • WHERE author = 'JACK LONDON'; • SET v_trunc = 1; • END;

21. Exception Handling contd… Example 2 • CREATE PROCEDURE DEPT_MEDIAN • (IN deptNumber SMALLINT, • OUT medianSalary DOUBLE) • LANGUAGE SQL • BEGIN • DECLARE v_numRecords INTEGER DEFAULT 1; • DECLARE v_counter INTEGER DEFAULT 0; • DECLARE c1 CURSOR FOR • SELECT salary FROM staff • WHERE DEPT = deptNumber • ORDER BY salary; • DECLARE EXIT HANDLER FOR NOT FOUND • SET medianSalary = 6666; • /* initialize OUT parameter */ • SET medianSalary = 0; • SELECT COUNT(*) INTO v_numRecords FROM staff • WHERE DEPT = deptNumber; • OPEN c1; • WHILE v_counter < (v_numRecords / 2 + 1) DO • FETCH c1 INTO medianSalary; • SET v_counter = v_counter + 1; • END WHILE; • CLOSE c1; • END;

22. Some tips for Exception Handling • At least one statement should exist inside the exception handler block. • For every SELECT statement and cursors exception handling should be carried out • Exceptions of following types should be specified in the exception handler statement. • SQLEXCEPTION • SQLWARNING • NOT FOUND • NOT FOUND is applicable only when the SELECT INTO statement or cursor is used • NO DATA EXCEPTION is not fired in DB2 so we cannot use user defined errors through exceptions as in Oracle

23. Effective Coding guidelines in V5R2 For example Assume Two Tables with following Description. Table1 - C1, C2, Gender, BeginDate, EndDate. (C1 is the primary key) Table2 - C1, C2, FKC1. (C1 is the primary key and FKC1 is a foreign key to Table1) • When joining tables, always specify the join condition first using only SQL variables. This helps ensure that the tables will be accessed properly Correct SELECT A.C1, B.C2 FROM Table1 A, Table2 B WHERE A.C1 = B.FKC1 AND A.C1 = :DG998-C1 Incorrect SELECT A.C1, B.C2 FROM Table1 A, Table2 B WHERE A.C1 = :DG998-C1 (No join conditions - only host variables) AND B.FKC1 = :DG999-C1

24. Effective Coding guidelines in V5R2 contd… • To ensure the developer does not forget to add adequate join conditions, it would be preferable to use the new join syntax available in DB2 V4.1 or later: • SELECT A.C1, B.C2 FROM Table1 A INNER JOIN Table2 B ON A.C1 = B.FKC1 WHERE A.C1 = :DG998-C1 • Additional host variables which may be specified on primary key columns, should be specified next. • Specify predicates in the most discriminating order.

25. Effective Coding guidelines in V5R2 contd… • Only select what you need; you do not need to SELECT columns just because they are in your WHERE clause (but they are required if you are specifying the column in the ORDER BY clause). • Always use the correct host variable type to hold a given SQL variable. If you are not sure what its type should be, then check an existing DCLGEN. • Do not use the ORDER BY clause unless it is actually needed. If order is important, then you should specify it; otherwise the order will not be guaranteed (For example, do not depend on a clustering index to guarantee order, because the access path or the attributes of the index may change.). • If possible, ORDER BY statements should only contain columns from the same table

26. Effective Coding guidelines in V5R2 contd… • If you are checking for an inclusive range BETWEEN is more efficient than >= and <=. Note: This is for an inclusive range. • Use • SELECT A.C1 FROM Table1 A WHERE CURRENT DATE BETWEEN BeginDate and EndDate • Instead of • SELECT A.C1 FROM Table1 A WHERE CURRENT DATE >= EndDate AND CURRENT DATE <= BeginDate

27. Effective Coding guidelines in V5R2 contd… • The IN clause is more efficient than OR clauses for checking a list of values; this aids in statement readability. • Use • SELECT A.C1 FROM Table1 A WHERE A.C1 IN (1, 2, 3, 4) • Instead of • SELECT A.C1 FROM Table1 AWHERE A.C1 = 1OR A.C1 = 2OR A.C1 = 3OR A.C1 = 4

28. Effective Coding guidelines in V5R2 contd… • If the ORDER BY clause contains columns from more than one table, • DB2 will always perform a sort. • Use • SELECT B.FKC1, B.C2 FROM Table1 A, Table2 B WHERE A.C1 = B.FKC1 AND A.C1 = :DG998-C1 ORDER BY B.FKC1, B.C2 • Instead of • SELECT A.C1, (redundant column being selected) B.C2 FROM Table1 A, Table2 B WHERE A.C1 = B.FKC1 AND A.C1 = :DG998-C1 ORDER BY A.C1, B.C2 (ordering by columns of two different tables)

29. Effective Coding guidelines in V5R2 contd… • If you can, convert negative statements to positive statements (e.g. use IN as opposed to NOT IN). A NOT will convert a Stage 1 predicate to a Stage 2 predicate resulting in degraded performance. NOT logic is typically more difficult to read. • Use DISTINCT only when necessary. The use of DISTINCT will always cause DB2 to perform a sort – even if it is impossible to for the result set to contain duplicates.