I have been poking around with Microsoft OneNote recently, and at first glance it appears to be a very powerful note taking app. You can dump stuff in from other Microsoft productivity applications like Word, Excel, and Outlook, and you can even create an Excel spreadsheet directly inside OneNote. It makes taking screen shots easy and has a two dimensional tabbing system that feels just like using a multi subject notebook with the capability of adding as many subjects as you want. I can see this being very useful for students. But with all that power, it feels unfinished to me. You can add notes, draw arrows and circles on top of them for added emphasis, take pictures and highlight the portion of the picture you want to bring attention to, but don't move anything around on the page because all those annotations are just a bunch of independent lines, boxes, and circles. They are not attached to anything, and if you move the big text box, you have to also move all the annotations as well. If you make a correction to your notes in a way that adds space to the text box, you now have to re-annotate the entire page because all your annotations are now in the wrong place. This is a major issue. There is no way to group drawings together (which can be done with the other Microsoft drawing tools), and there is no way to anchor a drawing to a bit of text or a table or an image, or anything else for that matter.
So bottom line is that OneNote is a simple little app that has a lot of potential, and a lot of headache. If you can deal with the pain, it might be able to help you organize your thoughts. I am going to try Evernote.
Tuesday, August 02, 2016
Monday, May 23, 2016
ILE RPG - Sub-procedures vs. Subroutines
When writing new RPG code, I use free format and sub-procedures exclusively. There is no advantage to using the older fixed format or subroutines. Fixed format limits the space you have for expressions, and prevents nicely indented control structures while there is nothing that a subroutine can do that a sub-procedure cannot. Even when maintaining existing code I tend to use free format and sub-procedures unless it is significantly more convenient to do otherwise. that being said, there are a few things to take into consideration. Particularly when it comes to using sub-procedures vs. subroutines.
Sub-procedures control scope as well as providing for code reuse. Subroutines are all about code reuse. It is the scope of a sub-procedure that makes is so useful. Items defined within a sub-procedure cannot be used or referenced outside it, and override similar items defined in the global scope. This is fairly well understood for variables defined with dcl-?. For example:
dcl-s ix Int(5) Inz(10);
procA();
dsply ix;
return;
dcl-proc procA;
dcl-s ix Int(5) Inz(1);
dsply ix;
end-proc;
Will result in:
1
10
Displayed on the console. Why? Because ix in procA is different from the global ix declared above. Sub-procedures allow me to pass in parameters, to allow even easier code reuse. No need to set some global variables, then call the subroutine, then retrieve the result out of other global variables. I simply pass the necessary values as parameters, and retrieve the result as a return value, or from one or more output parameters.
field1 = 'A';
field2 = 15;
exsr convert;
result = field3;
vs.
result = convert('A': 15);
It is this capability of sub-procedures that compels me at times to convert existing subroutines to sub-procedures. Particularly if a subroutine is working with a single set of values, and I need to call it with a different set of fields. This can be a simple conversion, just change BEGSR to DCL-PROC, ENDSR to END-PROC, add parameters, and pass them properly in the call. This is where a hidden feature of the subroutine becomes apparent. A subroutine is not a global entity. It is always scoped to a procedure, either the main procedure, or a sub-procedure. If you simply convert a subroutine to a sub-procedure, but the subroutine contained an EXSR op code, that called subroutine will suddenly be out of scope for the new sub-procedure. To fix this you will need to convert these subroutines called by the sub-procedure you just converted, and any subroutines called by them to sub-procedures as well. This additional work may take the use of a sub-procedure in this particular instance and put it in the realm where just setting and retrieving global variable values is significantly more convenient than converting all the necessary subroutines (and their calls) to sub-procedures.
Here is a quick diagram of how procedures, sub-procedures and subroutine scopes work within an RPG program.
Main Procedure {
subroutine A
subroutine A1
}
sub-procedure 1 {
subroutine B1
subroutine B2
}
sub-procedure 2 {
subroutine C1
subroutine C2
}
Anything in the Main procedure can call sub-procedure 1 or 2, and subroutines A1 or A2, but not subroutines B1, B2, C1, or C2. Sub-procedure 1 can call sub-procedure 2, and subroutines B1 and B2, but not any of the others, and likewise for sub-procedure 2. this is kind of sudo-code as curly brackets are not a part of the RPG syntax. In fact, a main procedure has no explicit starting point or ending point unless it is a liner main procedure. This is just an easy way to visualize the scope.
What do you think, was this helpful to you?
Sub-procedures control scope as well as providing for code reuse. Subroutines are all about code reuse. It is the scope of a sub-procedure that makes is so useful. Items defined within a sub-procedure cannot be used or referenced outside it, and override similar items defined in the global scope. This is fairly well understood for variables defined with dcl-?. For example:
dcl-s ix Int(5) Inz(10);
procA();
dsply ix;
return;
dcl-proc procA;
dcl-s ix Int(5) Inz(1);
dsply ix;
end-proc;
Will result in:
1
10
Displayed on the console. Why? Because ix in procA is different from the global ix declared above. Sub-procedures allow me to pass in parameters, to allow even easier code reuse. No need to set some global variables, then call the subroutine, then retrieve the result out of other global variables. I simply pass the necessary values as parameters, and retrieve the result as a return value, or from one or more output parameters.
field1 = 'A';
field2 = 15;
exsr convert;
result = field3;
vs.
result = convert('A': 15);
It is this capability of sub-procedures that compels me at times to convert existing subroutines to sub-procedures. Particularly if a subroutine is working with a single set of values, and I need to call it with a different set of fields. This can be a simple conversion, just change BEGSR to DCL-PROC, ENDSR to END-PROC, add parameters, and pass them properly in the call. This is where a hidden feature of the subroutine becomes apparent. A subroutine is not a global entity. It is always scoped to a procedure, either the main procedure, or a sub-procedure. If you simply convert a subroutine to a sub-procedure, but the subroutine contained an EXSR op code, that called subroutine will suddenly be out of scope for the new sub-procedure. To fix this you will need to convert these subroutines called by the sub-procedure you just converted, and any subroutines called by them to sub-procedures as well. This additional work may take the use of a sub-procedure in this particular instance and put it in the realm where just setting and retrieving global variable values is significantly more convenient than converting all the necessary subroutines (and their calls) to sub-procedures.
Here is a quick diagram of how procedures, sub-procedures and subroutine scopes work within an RPG program.
Main Procedure {
subroutine A
subroutine A1
}
sub-procedure 1 {
subroutine B1
subroutine B2
}
sub-procedure 2 {
subroutine C1
subroutine C2
}
Anything in the Main procedure can call sub-procedure 1 or 2, and subroutines A1 or A2, but not subroutines B1, B2, C1, or C2. Sub-procedure 1 can call sub-procedure 2, and subroutines B1 and B2, but not any of the others, and likewise for sub-procedure 2. this is kind of sudo-code as curly brackets are not a part of the RPG syntax. In fact, a main procedure has no explicit starting point or ending point unless it is a liner main procedure. This is just an easy way to visualize the scope.
What do you think, was this helpful to you?
Thursday, April 14, 2016
Resequence Records in a Table Using SQL
Every now and then I hear a question like "How can I reset the sequence numbers in my file using SQL?" If you are using DB2 for i, there is a simple solution involving OLAP specifications. These are expressions you can use to query things like rank, dense rank, and row number. It is ROW_NUMBER that we are specifically interested in here. Lets set up a sample table:
create table resequence_sample (
id long int primary key,
order_number dec(7,0) not null,
detail_line dec(5,0) not null,
seq_number dec(5,0) not null,
...
)
So you can see this table has a primary key named id an order number, a detail line number, and a sequence number for sorting the records, among other fields not named. Periodically this sort sequence gets gaps due to deletions, or other operations on the file, and we want to close those gaps up. If I were using RPG and record level access methods, I could simply read the records in order, and reassign seq_number from a counter starting at 1 and continuing until the rows were read. I might also want to reset back to 1 for each new detail_line and order_number. It would look something like this:
counter = 0;
read record;
dow not %eof();
counter += 1;
if order_number <> sv_order_number or
detail_line <> sv_detail_line;
counter = 1;
sv_order_number = order_number;
sv_detail_line = detail_line;
endif;
seq_number = counter;
update record;
read record;
enddo;
This is pretty simple and performance is lightening fast. But what if you want to do this with SQL? Well as I mentioned earlier you can use an OLAP feature of the database called ROW_NUMBER to do the same thing. It would look like this:
update resequence_sample s
set s.seq_number =
( select x.row
from
( select row_number()
over(partition by order_number, detail_line
order by seq_number)
as row,
a.id
from resequence_sample a
) x
where x.id = s.id
)
The magic is in the inner subselect which returns a list of record ids with it's row_number with breaks (partition by) on order_number and detail_line, sorted by (order by) seq_number. Notice the syntax of ROW_NUMBER expression. It is row_number() over(...). The clauses that you can put in the parenthesis of over() determine how the row numbers are assigned. Partition by defines how to group the selected records. Each group get's it's own sequence of numbers starting with 1. You could for example assign a new group of sequence numbers to each order, or as we have done above, to each order detail line. Or if you leave out the partition by clause entirely, you get a single sequence of numbers starting with 1 and incrementing by 1 to the last row selected. Order by allows you to select the sequence of records within each group. This is for numbering purposes only, and is independent of the statement order by. So you could select employee records ordering by name, but assigning row numbers by salary. This makes more semantic sense with the RANK and DENSE_RANK, but it works the same way with ROW_NUMBER.
One last point. You may be wondering why we didn't just put that where x.id = s.id on the inner subselect, and remove the outer subselect all together. The reason is in the way row numbers are assigned. That is by group of selected records starting with 1. If we moved the were clause to the inner subselect, only a single record would be selected there causing the row_number to always be 1. Pulling that where clause out one level allows us to assign row numbers to a group of rows, and then select the row we want the sequence number for. DB2's optimizer is smart enough that it doesn't perform that query more times than it needs to.
This example only shows the business end of the code for RPG. There are various declarations required to support the file and control variables to make it work that I have chosen to leave out. However the SQL example will run as is once you create the table.
create table resequence_sample (
id long int primary key,
order_number dec(7,0) not null,
detail_line dec(5,0) not null,
seq_number dec(5,0) not null,
...
)
So you can see this table has a primary key named id an order number, a detail line number, and a sequence number for sorting the records, among other fields not named. Periodically this sort sequence gets gaps due to deletions, or other operations on the file, and we want to close those gaps up. If I were using RPG and record level access methods, I could simply read the records in order, and reassign seq_number from a counter starting at 1 and continuing until the rows were read. I might also want to reset back to 1 for each new detail_line and order_number. It would look something like this:
counter = 0;
read record;
dow not %eof();
counter += 1;
if order_number <> sv_order_number or
detail_line <> sv_detail_line;
counter = 1;
sv_order_number = order_number;
sv_detail_line = detail_line;
endif;
seq_number = counter;
update record;
read record;
enddo;
This is pretty simple and performance is lightening fast. But what if you want to do this with SQL? Well as I mentioned earlier you can use an OLAP feature of the database called ROW_NUMBER to do the same thing. It would look like this:
update resequence_sample s
set s.seq_number =
( select x.row
from
( select row_number()
over(partition by order_number, detail_line
order by seq_number)
as row,
a.id
from resequence_sample a
) x
where x.id = s.id
)
The magic is in the inner subselect which returns a list of record ids with it's row_number with breaks (partition by) on order_number and detail_line, sorted by (order by) seq_number. Notice the syntax of ROW_NUMBER expression. It is row_number() over(...). The clauses that you can put in the parenthesis of over() determine how the row numbers are assigned. Partition by defines how to group the selected records. Each group get's it's own sequence of numbers starting with 1. You could for example assign a new group of sequence numbers to each order, or as we have done above, to each order detail line. Or if you leave out the partition by clause entirely, you get a single sequence of numbers starting with 1 and incrementing by 1 to the last row selected. Order by allows you to select the sequence of records within each group. This is for numbering purposes only, and is independent of the statement order by. So you could select employee records ordering by name, but assigning row numbers by salary. This makes more semantic sense with the RANK and DENSE_RANK, but it works the same way with ROW_NUMBER.
One last point. You may be wondering why we didn't just put that where x.id = s.id on the inner subselect, and remove the outer subselect all together. The reason is in the way row numbers are assigned. That is by group of selected records starting with 1. If we moved the were clause to the inner subselect, only a single record would be selected there causing the row_number to always be 1. Pulling that where clause out one level allows us to assign row numbers to a group of rows, and then select the row we want the sequence number for. DB2's optimizer is smart enough that it doesn't perform that query more times than it needs to.
This example only shows the business end of the code for RPG. There are various declarations required to support the file and control variables to make it work that I have chosen to leave out. However the SQL example will run as is once you create the table.