Code with PR

While loop While loops are used when a block of code needs to be executed as long as the specific condition is True.  Syntax: while condition:     // code to be executed Before we see the examples of while loop, Below are the couple of points to remember.  If the condition is not satisfied (or False) on the first instance, code inside the loop will not be executed at all.  If the condition is satisfied (or True) for every iteration, Loop will run infinitely.  Let's have a look at the simple example.  In the above example,  Line - 1: Creating or Initializing variable to use in the condition or loop.  Line - 2: while 'condition', We are using condition 'i < 10'. Loop would repeat as long as the value of 'i' is less than 10.  Line - 3: print statement to print the value of 'i'. This is usually replaced with the required action in the program.  Line - 4: Incrementing the value of 'i' by '1'. Incrementing, Decrementing or Amending the...

Generate SQL Objects In the previous post we have seen how to generate DDL statements for a specific database object passed using GENERATE_SQL procedure.  In this post we will see how to generate DDL statements required to create group of objects at once using GENERATE_SQL_OBJECTS procedure.  This procedure accepts a table (with list of database objects) as an input and generates the DDL statements for the objects listed in the table.  Before we see how this procedure works, let's see how should we create the input table.  Input table should be created with the column names mentioned below.  OBJECT_SCHEMA OBJECT_NAME  SQL_OBJECT_TYPE OBJECT_SCHEMA (Object Schema/Library Name) Refers to the schema name of the object for which DDL is to be generated.  No special values (like *LIBL, *CURLIB) are allowed.  This would be ignored if the object type is schema.  OBJECT_NAME (Object Name) Refers to name of object for which DDL is to be generated. Spe...

Generate SQL Data Definition Language (DDL) is used to create Database objects.  If we have a database object and no corresponding DDL statements or If we have files created using DDS and needs to modify the files using DDL instead of DDS.  SQL Procedure 'GENERATE_SQL' generates the DDL statements required to recreate the database object.  This procedure offers flexible return methods and returns DDL statements either as a result set or to the specified source file member.  Let's have a look at an example to see how this works.  In the above example, we are generating the DDL source for the Physical File created using DDS (see below for DDS source).  Before we see what this procedure returns, Let's have a look at the parameters passed.  DATABASE_OBJECT_NAME - Name of the object name for which DDL source to be generated for. '%' can be specified if there are multiple objects starting with same name.  DATABASE_OBJECT_LIBRARY_NAME - Name of the lib...

For loop For loops are used when a block of code needs be executed over a range or list of values.  List of values can be a List, Tuple, Set, Dictionary or String.  Number of times the loop is repeated is based on the number of values present in an iterable.  Syntax: for element in iterable iterable refers to a range, list, tuple, set, dictionary or string.  element refers to the element from the iterable and can be accessed in the loop using this name.  Range Let's have a look at a simple example of for loop over a range of numbers.  In the above example, for loop is repeated 10 times (from 0 to 9) and for every occurrence number will hold the corresponding value through out the loop (i.e., on the first iteration 'number' will hold the value of '0', on second iteration 'number' will hold the value of '1'...).  List Let's have a look at another simple example of for loop over a list.  In this example, for loop is repeated for each element in th...

Spooled Files Spooled files are generated when when system writes (prints) the data to an Output Queue. These could be Reports generated by the programs using Printer File (PRTF), System dumps or Job logs.  Spool files usually doesn't occupy much system space and often ignored. But, they could occupy the considerable amount of time if the spool files hasn't been cleared for long time.  Spool files can be deleted by using DLTSPLF (Delete Spooled File) command.  One other way of deleting spool files is from SQL by using DELETE_OLD_SPOOLED_FILES procedure. This procedure is present in SYSTOOLS library. One good thing about this procedure is there is an option to preview.  Let's have a look at how this works.  In the above procedure call, we are only passing one parameter.  'PREVIEW' - By passing 'YES', this procedure won't delete any spool file and returns the list of spooled files matching with the selection.  We have not passed any selection to delete t...

List Comprehension List comprehension is one of the ways of creating list. List would be created as a result of some operation on a range, set or list based on some condition (optional).  This makes the list creation easier to code and easy to understand.  Let's have a look at an example to understand this better.  E.g.:  Create a list with the list of odd numbers less than 10.  We will first see how to create a list with out list comprehension.  In the above example we are doing in four steps.  Line - 1: Create an empty list by using square brackets.  Line - 2: for loop on a range of numbers till 10.  Line - 3: if condition to check if the number is an odd number.  Line - 4: add the odd number to the list using append() method.  Same can be done in a single statement with list comprehension.  We have embedded for loop and if condition inside the square brackets directly to create a list with odd numbers.  let's break this...