• Let One Row Represent A Group, Take Three

  October 27, 2015 Ted Holt

  Faithful reader Bill Cisne sent me an email with an SQL SELECT query. No comment, no explanation, just code. Bill had figured out how to use a lateral join to solve a problem and was kind enough to share his success with me. I have been aware of lateral joins for some time, but have not found a need for them in my work. Today I revisit a problem to illustrate one way that lateral joins can be useful. LATERAL is a type of for-each loop within a query. For each row that a SELECT statement (the “outer” query) returns,

  …

  Read more

• Questions About AES Encryption In RPG, DB2 for i, And The Web

  October 27, 2015 Michael Sansoterra

  Hey, FHG: We have never used encryption. Our new manager wants us to start using it on our IBM i 7.1 system. (For instance, we store passwords for our website members). I use the ENCRYPT_AES and DECRYPT_CHAR SQL functions in my RPG programs. However, with respect to AES encryption, our PHP web programmer is asking about how to generate an initialization vector and then save it later for decryption. Can you point us in the right direction? Finally, our manager doesn’t like the fact that the encryption key is in plain text in RPG source code. What are your

  …

  Read more

• Developers Can Improve Security and Reduce the Administrative Cost of Security

  October 20, 2015 Patrick Botz

  One simple tool allows developers to improve the security of their applications and associated resources and reduce the administrative cost of managing security for the whole system. That tool is adopted authority. Adopted authority is an important tool for developers to have in their toolkit because it allows them to build applications that ensure users never encounter an authority failure even when administrators use PUBLIC(*EXCLUDE) authority on all sensitive resources. The combination of adopted authority and PUBLIC(*EXCLUDE) significantly increases the level of security and decreases the cost of managing security. It’s true that system administrators can change programs to adopt

  …

  Read more

• CONST Doesn’t Mean You Can’t Change It

  October 20, 2015 Ted Holt

  You can pass parameters to an RPG subprocedure in three ways: by reference, by read-only reference, and by value. (I have written about this before.) My favorite method is read-only reference. I use it as often as I can. Would you believe that it is possible to change the value of a variable that is passed to a subprocedure by read-only reference? Since you would probably do so only inadvertently, it’s good to understand how it can happen. To pass a parameter by read-only reference, include the CONST keyword in the procedure prototype and the procedure interface. D Process pr

  …

  Read more

• DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 2

  October 20, 2015 Michael Sansoterra

  Continuing on from Part 1, this tip covers some new features recently made available in DB2 for i. SQL Function Parameter Limit User-defined functions capabilities in DB2 for i have received a huge boost in that the parameter list limit for a scalar function has jumped from 90 parameters to a whopping 1024 parameters. Likewise, the combined input parameter and return columns limit for a table function has increased from 125 to 1025. Off the top of my head I can’t think of an example when I would want to pass more than 90 parameters into a scalar function.

  …

  Read more

• Keeping Firmware Up To Date

  October 6, 2015 Pete Massiello

  Last month, Larry Bolhuis discussed PTFs and keeping them current. This is extremely important, and I want to continue along on that theme of staying current. So, this month we will discuss determining the level of firmware of your Flexible Service Processor (FSP). If you don’t know what level it is currently running, you won’t know if it is up to date or not. It’s just as important to keep this up to date as your PTFs, your release of IBM i, or your Hardware Management Console (HMC) firmware. In all my travels, it seems people aren’t keeping their

  …

  Read more

• Scratch Message Files? Why Not?!

  October 6, 2015 Ted Holt

  If you read this august publication, you probably know what a scratch file is. (For those of you who know nothing about computers and read this publication solely for its sterling literary merit, a scratch file is a database table or stream file that is used temporarily within a job.) Today I invite you to consider that the advantages of scratch files also extend to scratch message files. Consider superb RPG program SUPERBPGM2: ctl-opt dftactgrp(*no); dcl-pr SuperbPgm2 extpgm('SUPERBPGM2'); ouStatus likeds(Status_t); inPlant char(12); end-pr; dcl-pi SuperbPgm2; ouStatus likeds(Status_t); inPlant char(12); end-pi; dcl-ds Status_t qualified template; MsgID char( 7); ProgramName char(10); Plant

  …

  Read more

• DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 1

  October 6, 2015 Michael Sansoterra

  This is just another great DB2 feature tip, calling attention to some useful features available in the recent releases of DB2 for i. To determine whether you can use these recent features, check with your IBM i administrator to make sure your database group PTF levels are up to date. For IBM i 7.1 the database group PTF level should be at least 34, and for 7.2 the database group PTF level should be at least 5. VARCHAR_FORMAT_BINARY and VARBINARY_FORMAT Two new functions were added to the DB2 for i built-in function arsenal that will assist developers with the annoying

  …

  Read more

• Auditing Object Changes In A Production Environment

  September 29, 2015 Ron Adams

  Like many business environments today, my shop consists of just one person wearing many hats to support the IBM i system. This makes for a tight, controlled environment with that one person managing the system very efficiently. However, most auditors aren’t too pleased with this setup as it opens the door for potential violations with established compliance guidelines. Even though we are a small shop with minimal budget, we were able to easily develop a solution to satisfy the auditors. Here’s what we did. Most environments like ours are run by individuals with high integrity and dedication to their jobs

  …

  Read more

• Easy Conversion To ASCII

  September 29, 2015 Ted Holt

  The system we love so well has a long love affair with the EBCDIC collating sequence, but most of the world runs on ASCII and Unicode. Dealing with ASCII data has not been a trivial experience for RPG programmers, but IBM i 7.2 adds a feature that helps immensely. In ancient days, RPG programmers would call the QDCXLATE API, which uses a translation table, to convert EBCDIC to ASCII. This is probably not a good solution. IBM’s translation tables were good for their time, but times have changed. A modern solution is the iconv family of code conversion APIs, which

  …

  Read more

Previous Articles Next Articles