Guru: Odds And Ends

May 14, 2018 Ted Holt

Little things can make a big difference. Today I am pleased to present a few short tips that may make a difference for you. I hope you get something useful here. If you have short tips that you would like to share with your fellow readers, please email them to me and I’ll see what I can do. Have a wonderful day!

Hey, Ted:

I just read your article More Date And Time Conversions Using SQL. Just as aside, the scalar functions Decimal/Dec, Integer, and BigInt can convert times, dates and timestamps directly into a numeric representation in the ISO format, i.e. YYYYMMDD, HHMMSS, YYYYMMDDHHMMSS,NSNSNS. This feature was introduced sometime with release 7.2. Here’s an example:

With x (MyDate, MyTime, NumDate, NumTime, MyTimestamp)
       as (Values
             (Current_Date,
              Current_Time, 
              Cast(20180327 as Dec(8, 0)),
              Cast(100000 as Dec(6, 0)),
              Current_Timestamp),
             ('2018-03-01', 
              '12.00.00',
              Cast(20180301 as Dec(8, 0)),
              Cast(235659 as Dec(6, 0)),
              '20180301043025'))
Select Dec(MyDate)                  DateNum,
       Integer(MyDate)              DateInt,
       Dec(MyTime)                  TimeNum,
       Dec(MyTimestamp)             TsNum,
       Dec(MyTimestamp, 26, 12)     TsNum12,
       BigInt(MyTimestamp)          TsBigInt,
       Dec(Date(MyTimestamp))       TsDateNum,
       Dec(Time(MyTimestamp), 6, 0) TsTimeNum,
       x.*
  from x;   

–Birgitta Hauser

Thank you, Birgitta. I did not know that those functions would convert date-time data.

I read Susan Gantner’s article on using RDI debug without SEP’s. I sometimes like to use this method but I have one problem. Here is the scenario:

I go into Debug Configurations | IBMi Debug Job and create a new debug configuration. I specify the qualified job name, select the programs I want to debug, and click Apply, then Debug. Later on I want to add another program to debug. When I go back into the debug configuration I just created and try to add another program and click Apply, the system tells me the job is already being debugged. How can I add another program to debug?

–Greg

The way I most commonly add programs to an existing debug session, whether it’s an SEP-started session or a configuration-started session, is that I step into (F5) the program on a call from one of the programs I’m already debugging. That automatically adds the program to the debug session and opens the source for me to add breakpoints.

If it’s not feasible or practical for you to step into the program you want to add to a debug session, you can also use the Programs view/tab in the upper right side of the debug perspective (assuming yours still looks vaguely close to the default debug perspective.) I’ve attached a picture of the Programs view – it’s only present in the debug perspective when a debug session is active. There you can see what programs are in debug for the session already and you can add more. Add more by pressing the green plus sign (+) in the toolbar (upper right) and put in the program name. You can use a qualified name if necessary to get the right one.

After you’ve added a program, you may want to open the source in order to set a breakpoint in the code. A quick way to do that is to expand the program a couple of times until you get down to the level with .RPG (or whatever language it is), then double click to open the source. Now you can add breakpoints and go on with your debug session.

–Susan Gantner

Hey, Ted:

 I have another fun request for you, one I’m guessing you’ve already done many, many times. Using an SQL statement, I need to remove any character that is not a letter or digit from a column in a table. Everything else must go.

–Evan

This is another good use of regular expressions, Evan. I can illustrate with telephone numbers, which in the United States are made up of 10 digits. However, letters are assigned to digits 2 through 9 to provide an alternate way of representing a phone number.

Here’s some sample data.

declare global temporary table temp
 (Phone char(24)) with replace     
insert into session.temp values
('9374397925'),                
('800LOVEMYI'),                
('800 IBM SERV'),              
('(662) 324-0374')             

Here’s a query that shows the regular expression you need:

select phone, regexp_replace(phone,'[^0-9A-Za-z]','')
  from session.temp                                  

See Michael Sansoterra’s marvelous article about regular expressions for more information.

I’ll finish with a short LPEX tip.

You’re using LPEX to edit a CL program. You need to declare a variable that can hold 24 values of 72 bytes each. How do you calculate the size of that variable?

You have several options.

1. You can multiply 24 times 72 in your head. (Yeah, right.)
2. You can grab a piece of paper and a pencil and work the problem as you did in third grade. (How quaint!)
3. You can open the calculator application in Windows or your cell phone.
4. You can open a calculator in a Web browser.
5. You can reach into or across your desk for a calculator.

But those methods take too much time. The quick way is to press the Esc key to position within the command line, type calc 24 * 72, and press Enter.

LPEX displays the answer in decimal and hexadecimal on the message line, which is just above the command line.

Can’t see the message line? Type set messageline on into the command line and press Enter.

Want to turn on the message line by default? set default.messageline on

Calc supports decimal and hexadecimal literals, the usual unary and binary math operators, min and max functions, bitwise functions, trigonometric functions, and a random number function. For more information, visit the IBM Knowledge Center.

RELATED STORIES

More Date And Time Conversions Using SQL

RDI debug without SEP’s

IBM Knowledge Center – calc command