Ted Holt
-
Running Query Without Adopted Authority
February 9, 2005 Hey, Ted
Because of the Sarbanes/Oxley Act, we are tightening our security. One problem we have is using AS/400 Query. Several of our menus include options that run the Work with Queries command. The problem is that a user who takes one of these options is running WRKQRY under adopted authority, and therefore has access to files that should from now on be secured. How can we change the WRKQRY command so that the user does not have adopted authority?
–Lin
You can’t change the command. You may be able to change the command-processing program, QSYS/QQUDA, but I don’t like to monkey
-
Repeated Characters in SQL
February 2, 2005 Hey, Ted
Using SQL, how do I fill a 128-byte character field with asterisks? Please don’t tell me I have to key every one of those puppies into my SQL command.
–Jack
I’ve got good news. Use the SPACE function to generate 128 spaces. Use the TRANSLATE function to convert the spaces into asterisks.
insert into somefile (somefield) values (translate(space(128),'*',' '))Or use the REPLACE function to convert the spaces into asterisks.
insert into somefile (somefield) values (replace(space(128),' ','*'))I haven’t run any speed tests, but TRANSLATE seems to run faster than REPLACE.
If you had wanted to fill your field with
-
Yet Another Way to Build CSV Files
February 2, 2005 Hey, Ted
I know offshoring gets a lot of blame for the decline in programmer positions these days, but there’s another culprit that I never hear about: the vast array of reporting and data-mining tools available to users.
Twenty years ago a programmer had to write code to build a report program. Now users create their own reports with Microsoft Excel. In my shop, that means we have lots of programs that create CSV (comma-separated values) files. Let me add a simple but effective technique to the Copy to Import File (CPYTOIMPF) command and the plethora of third-party products that create files
-
Another Reason to Use P-Fields
January 26, 2005 Hey, Ted
Within a subfile, I am using an indicator to highlight a field if the value of that field is invalid. Is there a way, when I read a subfile record, to analyze the attributes of the field to see if the indicator was switched on or off when the subfile record was written? My problem is that the display attribute is losing its original setting.
–Alan
I don’t know of a way to determine the original setting. I have included hidden one-byte fields in a subfile to store the settings of indicators so I could reset the indicators before updating
-
The Save-Restore Commands
January 26, 2005 Hey, Ted
We have a new machine and have joined the world of logical partitioning. We have a partition for production and one for development. I am struggling with copying data and source code between the two machines. FTP copies source members without a problem, but it drops the modification dates on the source records. To copy data files, I’ve been transmitting save files in binary mode, which is a hassle. Also, I’d like to set up a scheduled job to refresh certain files on the test system from the production system at night, and while FTP would work, it would be
-
Extracting Zoned and Packed Decimal Values from Character Fields
January 19, 2005 Hey, Ted
I have a physical file that includes a large alphanumeric field that is defined differently for many different record types. I am creating logical files for each of the record types and must redefine this large field in different ways for different record types. For some of the record types the field contains packed decimal values. How do I define a substringed field (one created with the SST keyword in DDS) as packed decimal?
–Mary
Your situation is not unusual, especially in shops that use packaged software. Since developers can’t foresee everything users may have to store, they sometimes include
-
The Dangers of Temporarily Changing User Profiles
January 12, 2005 Hey, Ted
In “Monitoring for System Request Menu Option 2,” you answered a question from Paul, who needed to write a CL program that temporarily changes a user profile. I always worry when I hear someone is going to temporarily change a user profile and then change it back. Lots of things could happen that would cause problems. Paul realized System Request menu option 2 (ENDRQS) could cause problems that would prevent him from returning the user profile to its original state. What neither you nor Paul mentioned, however, is that options 1 and 90 from the System Request menu could also
-
RPG IV Comment Blocks
December 8, 2004 Hey, Ted
Conditional compilation directives (/IF DEFINED) have two good uses that I have not seen mentioned in any of the midrange-oriented e-mail newsletters. Maybe you would like to tell your readers who work in RPG shops about them.
One good way to use a conditional compilation directive is to comment-out large sections of source code. Here’s an example.
/if defined(TheFollowingIsCommentedOut) C eval *in21 = *on C eval *in22 = *off C* eval *in23 = *on C if *in24 C exsr DoSomething C exsr DoSomethingElse C endif /endif
I prefer this technique to that of commenting out each line individually. For one
-
Let Me Out of Here!
November 10, 2004 Hey, Ted
Having read your article “Subprocedures: Better than Subroutines,” I have been inspired to use subroutines less and subprocedures more in RPG programs. However, I have come up against a problem that I can’t solve.
In many of my programs I include a clean-up subroutine that runs when the program ends, whether the program ends normally or abnormally. The subroutine includes various house-keeping tasks, sets on the LR indicator, and executes a RETURN operation. You probably know what happens when I implement such a routine in a subprocedure. Instead of returning to the calling program, control returns to the calling routine
-
More Conditional Sorting with SQL
November 10, 2004 Hey, Ted
Your tip “Conditional Sorting with SQL” is one cool tip! May I add two more examples?
My examples are in RPG, but I assume they will work with any language. In both examples, I use host variables to control the sort.
Host variable SortOption controls the sort in the first example. If host variable SortOption has a value of 1, the data is retrieved in order by customer name. If two customers have the same name, they are retrieved in customer number sequence. If SortOption has any other value, the data is retrieved in customer number sequence.
D SortOption S