QADBIFLD – Field level dictionary on ISeries

The QADBIFLD file in QSYS library contains all the field and files that are on the I5.       It’s uses are unlimited.       If your an ISeries expert you need it in your arsenal.

 

Some other useful ones are QADBFDEP, QADBLDEP, QADBXFIL,   QADBXREF (Cross Reference Physicals and Logical)

Excel formula to round to nearest .09 cent.

This formula is really handy to calculate pricing up to the nearest .09 cent.

 

=CEILING(D15+0.01,0.1)-0.01

 

 

 

RPG Blast from the past

While doing a little cleaning around the office, we uncovered some RPG programming pads.     These are for programming punch cards.       Back in the old days you actually wrote out your programs these forms and then punch cards.      This was actually a little bit before my time however.   These documents where from IBM but I didn’t see a a copyright on them.

GX21-9092-4 RPG Control Card and File Description Specifications

GX21-9094-2 RPG Input Specifications

ISeries Printer document

For help configuring printers on ISeries, the best place to start is http://t.co/akv7kyW (IBM’s official document.    It is called Information on Printers from Various Manufacturers)

Excel VBA to query ISeries DB2 connection using connectionless ISeries ODBC Driver

Below is a VBA subroutine executed from excel to query an ISeries
table using the ISeries Access ODBC Driver. I need to clean up
a little and declare my variables etc, but it works. I’ve always
found it difficult to get a complete example of this on the Web.
I’m sure it’s out there but searching for me has been time consuming.
I’ve had to put bit’s and pieces together to get it to work the way I want.
It’s nice because the ODBC connection is connectionless so the user doesn’t
have to have it defined on their machine. s long as the have client access
and a login it should work.

Sub transfer()

Dim varState As String

Set CS = CreateObject(“ADODB.Connection”)
Set RS = CreateObject(“ADODB.Recordset”)

‘Retrieve values from Cells on the sheet to use as selections in the query below
varState = ActiveSheet.Range(“C2”).Value
varFrom_Date = ActiveSheet.Range(“B3”).Value
varTo_Date = ActiveSheet.Range(“B4”).Value

‘ ISeries connection String
‘ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0”
ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;Library=PWRDTA41;QueryTimeout=0”

CS.Open (ConnectString)

SqlString = “SELECT hhicusn , C.FFDCNMB, C.FFDSTEB, hhiclsn, SUM(hhiqysa), SUM(hhiexsn), SUM(hhiexac) ” & _
” FROM pwrdta41.hhiorddp ” & _
” left outer join PWRDTA41.FFDCSTBP c” & _
” ON hhicusn = c.ffdcusn and” & _
” hhidivn = c.ffddivn and” & _
” c.ffdcmpn = hhicmpn and” & _
” c.ffddptn = hhidptn” & _
” WHERE hhidtei between ” & varFrom_Date & ” and ” & varTo_Date & _
” and hhiclsn = ‘105’ and c.ffdsteb = ‘” & varState & “‘” & _
” GROUP BY hhicusn, c.ffdcnmb, c.ffdsteb, hhiclsn” & _
” ORDER BY hhicusn”

‘Message box can be used for debugging the SQL statement
‘MsgBox (SqlString)

RS.Open SqlString, CS

‘Clear Previous contents of Cells
Cells.Select
ActiveSheet.Range(“A7:Z65535”).ClearContents

‘copy the Recordset to excel sheet starting at A7
ActiveSheet.Range(“A7”).CopyFromRecordset RS

‘Close Connection and RecordSet
RS.Close
CS.Close

‘Reset cell back to A1
ActiveSheet.Range(“A1”).Select

End Sub

ISeries access toolbar

To add the Microsoft office toolbar back in go to options and add the add-in cwbtfxla.xll from the /Program Files/IBM/ISeries  Access/shared folder.

Clean and Clear nohup.out

nohup.out logs terminal commands of processes in linux. It can grow quite large and can be cleaned and cleared with the following command.

cat /dev/null > nohup.out

Displaying cron jobs for all users

for user in $(cut -f1 -d: /etc/passwd); do crontab -u $user -l; done

Summing rounded numbers in Excel

Use the array function {=SUM(ROUND(A1:a10,2))}

Remember to do the CTRL+SHFT+ENTER to enter the array function

Links:

http://www.exceltip.com/st/Summing_Rounded_Numbers/73.html

http://www.exceltip.com/st/Summing_Rounded_Numbers/1062.html

Filling in blank values based on cell aboves value

Copied from:

http://www.mrexcel.com/articles/fill-pivot-blank-cells.php

 

 

Trick #1. Selecting all of the blank cells in that range.

• Hit Ctrl+G, Alt+S, K and then enter. huh?
• Ctrl G brings up the GoTo dialog
• Alt+S will pick the “Special” button from the dialog box
• The Goto-Special dialog is an awesome thing that few know about. Hit “k” to pick “blanks”. Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.

Trick #2. Don’t watch the screen while you do this – it is too scary and confusing.

Hit the equals key. Hit the Up arrow. Hold down Ctrl and hit enter. Hitting equals and the up arrow says, “I want this cell to be just like the cell above me.” Holding down Ctrl when you hit enter says, “Enter this same formula in every selected cell, which, thanks to Trick #1 is all of the blank cells which we wanted to fill in.

Trick #3. Which Jennifer already knows, but is here for completeness.

You now need to change all of those formulas to values. Select all of the cells in A3:B999 again, not just the blanks. Hit ctrl+C to copy this range. Hit alt+e then sv (enter). to Paste Special Values these formulas.

Ta-da! You will never spend an afternoon manually pulling down column headings in a pivot table again.