While loop in Python

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 value of the variable used in the condition is essential is most of the cases. Otherwise, there is a risk of loop running infinitely. 

We can use either True or False instead of condition. However, mentioning 'True' would cause the loop to run infinitely and 'False' would cause the loop to not run at all. 

In this example, while loop will run infinitely. 

break statement

In the above example, loop is run infinitely. We can use the break statement to exit from the loop. 

No statement in the while loop is executed after the break statement.

In the above example, loop would be repeated until the break statement is executed (i.e., when condition 'i == 10' is satisfied).

continue statement

break statement would terminate the loop execution. But, if we need to skip the processing for that particular iteration and continue with the loop, continue statement can be used. 

None of the following statements would be executed in that iteration and next iteration would continue normally. 

In the above example, 'print' statement would not be executed when value of 'i' becomes '5'. 

pass statement

pass statement works like a place holder if no specific operations is required and is mandatory by syntax. 

In the above example, No action is required when value of 'i' becomes '5'. We can use 'pass' statement.

else statement

Else in while loop isn't exactly same as if-else. Instead this is executed once the while loop is completed. 

In the above example, statements in the else block are executed once the while loop is completed. All the numbers from 0 to 9 would be printed followed by "While loop is Completed" in else block.

One thing to remember here is 'else' part would only be executed if the while loop is executed completely and not terminated by using 'break' statement. If break statement is executed, else part wouldn't be executed.

In the above example, statement in the else block would not be executed as the break statement is run.

Nested while loops

Like Nested if, while loops can be nested in other while loop. 

In the above example, 

• Line - 3: First while loop is repeated as long as 'i < 3' is satisfied and loop will be terminated once i becomes '3'. 
• Line - 4: Variable 'j' is created with '0'. This is executed for every iteration of the previous loop.
• Line - 5: Second while loop is repeated as long as 'j < 3' is satisfied and loop will be terminated once j becomes '3'.
• This loop is run for every iteration in the first loop. 

Result would be printed like below. 

One thing to note here is, break and continue statements would only apply to the loop the statement is executed on (i.e., closest loop). 

In the above example, break statement would terminate the inner loop and outer loop would still continue normally. 

Hope the above was a bit of help to understand the while loops in Python. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

Generate SQL for Database Objects - Part 2

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.
• Specific Name of Procedure or Function to be used for Procedures and Functions.
• For Table or View, Object must not refer to the alias. 
• Extract object name to be mentioned and no '%' is allowed to indicate the list of objects starting with specific name. 

SQL_OBJECT_TYPE (SQL Object Type)

• Refers to type of the object for which DDL is to be generated.
• Below are the valid SQL Object Types. 
• ALIAS - Object is an SQL alias.
• CONSTRAINT - Object attribute is a constraint.
• FUNCTION - Object is an SQL function.
• INDEX - Object is an SQL Index.
• MASK - Object is an SQL column mask.
• PERMISSION - Object is an SQL row permission.
• PROCEDURE - Object is an SQL procedure. 
• SCHEMA - Object is an SQL schema (or library).
• SEQUENCE - Object is an SQL sequence.
• TABLE - Object is an SQL table (or physical file).
• TRIGGER - Object attribute is a trigger. 
• TYPE - Object is an SQL type. 
• VARIABLE - Object is an SQL global variable. 
• VIEW - Object is an SQL view.
• XSR - Object is an XML schema repository object. 

Let's create the input table and add few database objects into this table.

We are using the same names as described above to create the input table. 

We are inserting objects of different types into the input table created. 

• Line - 7: Inserting the logical file 'TESTLF', library 'REDDYP1' and SQL object type 'INDEX'. 
• Line - 8: Inserting the specific name 'QSQGENSQLO' of SQL procedure 'GENERATE_SQL_OBJECTS', library 'QSYS2' and SQL object type 'PROCEDURE'. 
• Line - 9: Inserting the physical file 'TESTPF', library 'REDDYP1' and SQL Object type 'TABLE'.
• Line - 10: Inserting the schema (library) 'REDDYP1', library 'QSYS' and SQL Object type 'SCHEMA'. For schema, library name would be ignored. 

One thing to note here is duplicate entries are not allowed. 

We aren't adding objects in any specific order. However, GENERATE_SQL_OBJECTS procedure would generate the DDL statements for each object in the below order. So that if any dependent objects are present depended object would be created first (E.g.: Schema and Table or Table and View etc.) 

• Schemas
• Tables
• Sequences
• Aliases
• Non-MQT (Materialized Query Tables) tables, any constraints and indexes on these tables
• Functions
• Procedures
• Variables
• Views, Logical files, MQTs. and any constraints or indexes on these tables
• Triggers
• Masks
• Permissions
• XSR Objects

Let's now call the procedure GENERATE_SQL_OBJECTS by passing the input table with the list of database objects and see how the DDL statements would be generated. 

Below are the parameters we are using in the above query. 

SYSTEM_TABLE_NAME 

Name of the table with the list of database objects for which DDL statements are to be generated. See above for more details on how the table should be created. 

This is the only mandatory parameter. Default values would be considered for all other parameters if not passed. 

SYSTEM_TABLE_SCHEMA

Name of the schema in which the table (passed in the previous parameter) is present in. 

Default value 'QTEMP' would be considered if not passed. 

DATABASE_SOURCE_FILE_NAME

Source file name in which DDL statements are to be generated. 

Default value 'Q_GENSQOBJ' would be considered if not passed. 

DATABASE_SOURCE_FILE_LIBRARY_NAME 

Library name of the source file (passed in the previous parameter) in which DDL statements are to be generated. 

Default value 'QTEMP' would be considered if not passed. 

DATABASE_SOURCE_FILE_MEMBER

Member name of the source file (passed in the previous parameter) in which DDL statements are to be generated. 

Default value 'Q_GENSQOBJ' would be considered if not passed. 

If Database source file name, library and member aren't passed, statements would be generated in the default source member in QTEMP and the same would be returned as result set. 

If these values are passed, member should exist on the system. 

There are many other parameters we can consider passing based on the requirement and most of them are similar to GENERATE_SQL procedure.

Let's have a look at the DDL statements generated and the order they are generated. 

DDL for Schema is generated first.

DDL for Table is generated after Schema. 

DDL for Procedure is generated after Table.

DDL for Index is generated after Procedure.

All of these are generated in the single source member passed in the parameters. 

Full list of parameters can be found here. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

Generate SQL for Database Objects - IBM i

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 library name in which data base object is present in. '%' can be specified if the object is to be considered from multiple libraries starting with same name. 

DATABASE_OBJECT_TYPE - Type of the database object passed. Please note that this is SQL object type (E.g.: 'TABLE' is to be used if generating the DDL for Physical File). 

Below are some of the object types allowed. 

• TABLE - Object is an SQL table or Physical file. 
• VIEW - Object is an SQL view or Logical file.
• PROCEDURE - Object is an SQL procedure. 
• FUNCTION - Object is an SQL function. 

Full list of allowed object types can be found here. 

The above three parameters are mandatory and procedure would fail if not passed. 

Let's now look at the result set returned. 

The result set contains,

• Source Sequence Number.
• Source Date. 
• Source Data.

Same information is stored in table Q_GENSQL (member - Q_GENSQL) in QTEMP library. Table QTEMP doesn't necessarily present by the time procedure is run. If the table is already present, data in the member would be replaced. 

Let's now have a look at the DDS source used to create this Physical File originally. 

In the above example, we aren't passing the source file details to where the DDL statements should be restored. 

If we pass the source library, file and member details generated DDL statements would be updated to the corresponding source member. One thing to remember is if we are passing the Source file and member, this should be present by the time procedure is called, otherwise GENERATE_SQL would throw error.

We will have a look at generating DDL statements for the procedure 'GENERATE_SQL' itself and store the data into source member passed. 

In the above example, we are generating the source for 'GENERATE_SQL' procedure into the Source file passed. 

Let's have a look at the additional parameters we are passing. 

DATABASE_SOURCE_FILE_NAME - Name of the source file to which generated SQL statements to be added. 

• Source file passed should be present on the system. 
• Length of the source file should be greater than or equal to 92. 
• Name of the source file is case sensitive (E.g.: If I pass 'qsqlsrc' instead of 'QSQLSRC' an error would be thrown.

DATABASE_SOURCE_FILE_LIBRARY_NAME - Name of the library in which source file is present in. Library name is case sensitive and should be present on the system. 

Apart from the library name, This parameter accepts two special values mentioned below.

• *CURLIB - Current Library
• *LIBL - Library List

DATABASE_SOURCE_FILE_MEMBER - Name of the source file member to which generated SQL statements to be added. This should be a valid member name, case sensitive and should be present on the system. 

This parameter accepts two special values mentioned below. 

• *FIRST - First member of the source file passed.
• *LAST - Last member of the source file passed. 

REPLACE_OPTION - Replace option specifies if the source file member should be replaced or if the generated statements to be added to the existing member. 

Valid values are,

• 0 - Resulting SQL statements are added to the existing member. 
• 1 (default) - Existing member would be cleared before adding the generated SQL statements. One thing to note here is member may be cleared even if there is an error while processing. 

If SQL statements are being generated for more than one object at a time, only the source for last member would be remained in the member (If '1' is specified).

NAMING_OPTION - Naming convention to be used in the generated SQL statements. Below are the valid values. 

• SQL - SQL naming convention like schema.table
• SYS - System naming convention like library/file

While looking at the above examples, we found that '%' can be used for identifying a database object that starts with specific name or database object library that starts with a specific name.

What happens if there are more than one object matching with this criteria? 

This would depend upon the replace option. If the replace option is not specified or specified as '1', then SQL statements generated for the last object would be returned in the result set or updated in the source member passed. 

All the previous members would be replaced by the subsequence occurrence. 

In the above example, 

• Line - 1: We are using '%' to find the data base objects that start with 'TEST'. 
• Line - 2: We are using '%' to find the data base object specified in the libraries that start with 'REDDYP'. 
• Line - 4: Parameter 'REPLACE_OPTION' plays important role here. 
• If '0' is specified, SQL statements generated for each object found would be appended to the result set or source member (QTEMP/Q_GENSQL (Q_GENSQL) as no source file/member is passed). 
• If '1' is specified, SQL statements generated for each object would replace the existing data and will result in retaining the data for last object.

Apart from these, we are passing two additional parameters here. 

DROP_OPTION - Drop option specifies if the DROP statement should be generated before the CREATE statement. 

Valid values are,

• '0' (default) - DROP statement should not be generated. 
• '1' - DROP statement should be generated. 

CREATE_OR_REPLACE_OPTION - Create or Replace option specifies if the CREATE OR REPLACE statement should be generated or just CREATE statement. 

Valid values are, 

• '0' (default) - CREATE OR REPLACE should not be generated. 
• '1' - CREATE OR REPLACE should be generated. 

Result to the above query would only contain the source for the last object found and would contain both DROP statement and CREATE OR REPLACE statement. 

Apart from the parameters mentioned above there are many more useful parameters allowed by this procedure. Full list of parameters can be found here. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

For loop in Python

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 the list (new_list) and corresponding value of the element can be accessed by using 'list_element' through out the loop. 

We are able to access the elements from a list directly. There is one other way of using for loop on a list is by using range.

In the above example, Instead of repeating the for every element in the loop, we are repeating the loop on the index (by calculating the length of a list).

• 'len' function would return the length of the list. 
• 'index' represents the index of the list for every iteration. 
• Elements of a list can be accessed by using index (new_list[index]).

Result of this example is same as the previous. But, this becomes helpful we are to consider index for the operation inside the loop. 

For loop over a tuple is same as list. 

Set

Let's have a look at an example of for loop over a set. Unlike List and Tuple, Set is an unordered collection of data. Data is stored in no specific order and cannot be accessed by index. 

Just like for loop over a range and list, for loop is repeated for all the elements in a set. Because of the way data is stored in a set, Data returned could be in any order and the result could vary every time the program is run. 

Dictionary

Dictionary is different from other iterables due to the way data is stored (key and value combination). 

For loop on a dictionary would only return it's key. 

In the above example, for loop is repeated for every key in the dictionary and data in the dictionary can be accessed by using corresponding key. 

String

Just like other lists, Strings are also iterable objects in Python and for loop can be used in the same way on strings as well. 

For strings, each character would be considered as a separate element and for loop is repeated for all the characters in the string passed. 

Nested For loops

Nested for loops can be used if we have an iterable inside another iterable. E.g.: List inside a list or String inside a List.

Let's have a look at an example using the List. 

In the above example,

• Line - 2: 'new_list' is created with list of strings.
• Line - 5: for loop is repeated for every string in the list. 
• Line - 7: for loop is repeated for every character for every list element (string).

break statement

In all the examples we have seen, for loop is executed for all the elements in the iterable. This doesn't necessarily needed always, we can use break statement. 

Whenever break statement is executed, loop will be terminated and no further statements would be executed.

In the above example, for loop is repeater over a range of 10, once the number is '5', loop will be terminated. 

continue statement

break statement would terminate the entire loop. Where as continue statement would only terminate the current iteration and continues the loop from the next iteration.

In the above example, print statement won't be executed for number '5'.

pass statement

pass statement works like a place holder if no specific operations is required and is mandatory by syntax. 

In the above example, 'pass' statement would work as a place holder to avoid any error. 

Else 

Else in for loop isn't exactly same as if-else. Instead this is executed once the for loop is completed. 

One thing to remember here is 'else' part would only be executed if the for loop is executed completely and not terminated by using 'break' statement. If break statement is executed, else part wouldn't be executed as well.

In the above example, else is executed once for loop is completed (after all the elements in the list).

Hope the above was a bit of help to understand the for loops in Python. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

Delete Old Spooled files from SQL - IBM i

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 the spooled files. So, procedure would consider the default parameters to delete or preview the spool files to be deleted. 

Below are the other parameters for this procedure. 

DELETE_OLDER_THAN -  Timestamp needs to be passed as a parameter. This would be considered as the starting point for deleting the spool files. Any spool file older than this timestamp would be considered for deletion. 

Default value for this parameter is 'CURRENT TIMESTAMP - 3 MONTHS'. If we don't pass this parameter all the spool files older than 3 months would be considered for deletion. 

P_OUTPUT_QUEUE_NAME - Name of the Output Queue. Spool files from this OUTQ would be considered for deletion. 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from all the OUTQs would be considered for deletion. 

P_OUTPUT_QUEUE_LIBRARY_NAME - Name of the library Output Queue is present in. Spooled files from all the output queues from this library would be considered for deletion (If OUTQ name has been passed only that specific OUTQ would be considered). 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from the OUTQs in all the libraries would be considered for deletion. 

P_USER_NAME - Name of the user whose spooled files are to be deleted. All the spooled files for this user would be considered for deletion.

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files for all the users would be considered for deletion. 

Let's have a look at an example by passing these parameters. 

In the above example, we are passing all the parameters and procedure would return the list of spooled files matching with the parameters passed. We can just pass the parameters that are required and leave the remaining to default. 

This would return the list of spooled files that are considered for deletion (PREVIEW => YES). 

Passing 'NO' to PREVIEW or removing PREVIEW parameter would delete these spool files. 

One thing to note is that, User should have sufficient authority to the spooled files to be deleted. 

• User should have authority to the DLTSPLF command. 
• User should have authority to the QSYS2/OUTPUT_QUEUE_ENTRIES_BASIC view. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

List Comprehension in Python

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 statement and see in detail. 

odd_numbers = [number for number in range(10) if number % 2 != 0]

• square brackets [] to create the list. Similarly {} and () can be used as well to create dictionary, set or generator. We will see more about set and dictionary comprehensions towards the end.
• number highlighted in green indicate the value to be added to the list. This can include some arithmetic operation as well, we will see an example by using some operation.
• for number in range(10) to loop through the range from 0 to 9. Any iterable can be used in place of range). 
• if number % 2 != 0 condition to identify the odd number. 

Let's have a look at another example to create a list with squares of odd numbers less than 10. 

We have replaced number with 'number * number' to calculate the square of an odd number and add to the list. This can be any expression based on the requirement. 

We have looked at creating a list by using one iterable. We can use multiple iterables at the same time to create a list (or nested lists). 

Let's have a look at the example by using two lists. 

E.g.: 

Create a list with the product of each element in a list with the elements of another list. 

In the above example, 

• We are using one list (even_numbers) and one tuple (odd_numbers). 
• Two for loops to fetch each element in the list.
• 'for even in even_numbers' would be considered as the first for loop and for every even number in the list second loop would be repeated. 
• 'for odd in odd_numbers' would loop through odd_numbers for every element from the first loop.
• For each combination operation 'even * odd' is calculated and added to the list. 

This is same as using nested for loops with out using list comprehension. 

Both these examples would return the same list [2, 6, 10, 4, 12, 20, 6, 18, 30]. 

Similarly, we can create nested list (or matrices) using the same instead of creating product. 

We can also use the list comprehension to convert nested lists to a single list (matrix flattening). 

In the above example, 

• First for loop is repeated on the matrix.
• For every list from the matrix, second for loop is repeated and returns the element.
• element returned is added to the list created. 

List comprehension is very useful to create the lists, at the same time we need to be aware of not over using it (like using it on more number of lists). This would make this difficult to understand and maintain the code. 

One thing to remember is it becomes extremely difficult to debug when they are not working as expected, specially when we are using multiple lists.

So, it is best to decide whether to use list comprehension or not based on the functionality rather than to using it all the time.

Let's have a look at Set comprehension and Dictionary comprehension with simple examples. 

Set comprehension

Set comprehension is same as list comprehension, only difference is we use curly brackets {} instead of square brackets. Using round brackets () would create a generator not tuple, we will see more about generators in different post. 

Dictionary comprehension

Dictionary comprehensions are also similar to set comprehensions. Only difference is it requires the key to be added. 

Let's have a look at an example. 

In the above example, we are considering the number in the range as a key and square of the number as corresponding value. 

We can also use dictionary with the list of keys supplied from a list. 

In the above example, 

• We are creating a list with the keys required to be in the dictionary. 
• Using List comprehension to get the squares of odd numbers in the range of 10. 
• Dictionary comprehension to loop through the list of keys and assign the output of list comprehension as a value to the key. 

Hope the above was a bit of help to understand the List comprehension in Python. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.