IBMi Information Tech in the trenches

Search text inside files - Windows 10 Indexing

2023-08-05T05:43:00.000-04:00

Have you ever encountered a situation where you were unable to find specific content within your files using Windows file explorer? It can be frustrating, especially when you know the text you are searching for exists within a file in the folder. This issue often arises after Microsoft updates, which may revert Windows settings to their default configurations.

To address this inconvenience in the future, I'd like to share a simple solution that enables Windows 10 file explorer to search for text inside files. First, navigate to the Windows search bar and type "indexing." Select the "Indexing Options" from the search results. This will open the Indexing Options window.

Once you're in the Indexing Options, click on the "Advanced Options" button. Within this window, you'll find the "File Types" tab. Here, you can see a list of file extensions that are currently included in the search indexing process. To enable text search within files of specific extensions, find the extensions you want to include.

For each extension, modify the radio button labeled "How should this file be indexed" to the "Index Properties and File Contents" option. This change will ensure that the content within those files is indexed and becomes searchable using the Windows file explorer.

By following these steps, you can enhance your Windows file explorer's functionality, enabling it to search for text inside various files efficiently and saving you time and frustration when locating specific information in the future.

Richard

Upgrade IBM i Operating System to 7.4

2023-06-13T15:18:00.006-04:00

Hi All,

Currently planning an upgrade from IBM I5 V7R3 to V7R4. I have been a long time since I have done an upgrade myself. But, it's like riding a bicycle; you do not forget some things.

Thank you, IBM, for providing an excellent guide for the update.

Upgrade IBM i Operating System to 7.4.

Best,

Richard

IBM i IFS mapped drive - Access Denied

2023-04-18T17:26:00.000-04:00

The other day I had an issue configuring a mapped drive to the IBM i IFS. The error generated stated access was denied and not allowing me to map a drive from Windows.

I could log on to the green screen, run WRKLNK for the share I attempted to map, and connect to Navigator with no problem. My profile was not disabled on the Net Server.

I could map to the IFS with an alternate generic profile with no issue.

Very strange. I searched Google for anything I missed and found I had covered all the bases.

A little later, I was chatting with my good buddy Jason, and I mentioned how this system does not have complexity rules like his system, and as the words came out of my mouth, the light bulb lit up.

My password is mixed case, and I must use all lowercase when I need to map a drive. I sure miss single sign on.

Learn something new every day. Actually, probably something I had forgotten long ago.

~Richard

STRDBGSVR not starting

2022-11-30T11:49:00.005-05:00

Power 8 in the cloud

RDI 9.0.6

When attempting to debug a program in RDI, I received a message the debug server is not started. I selected the option to start the debug server, and after five minutes, nothing happened, and the server had yet to start.

I switched to the 5250 green screen and entered STRDBGSVR, and after five minutes, the command disappeared from the screen. The debug server still needs to be started.

I hunted for job logs and could not find any, strange. I found where the system was trying to start QB5ROUTER but did not see it in active jobs.

At this point, I open a call with IBM for some help. Several weeks ago, a service company applied PTF's which required a restart. It is usual for me to start the debugger with STRDBGSVR after a system restart.

After a few minutes of working with IBM and scratching our heads, I reran the command and watched the job. After a few minutes job disappeared, but the server not started.

Come to find out, subsystem QUSRWRK did not start automatically as it should have, and there were several jobs in the job queue, all related to starting QB5ROUTER.

I started the QUSRWRK subsystem, and poof, all jobs were processed in seconds, and the debug server started.

Extract emailed attachment and upload to IBMi

2016-11-30T09:02:00.000-05:00

New challenge – Extract Excel spreadsheet of yesterday’s bank settled credit card transactions from an E-mailed zip file attachment, send spreadsheet to the IBMi, appending data to SQL table and launch balancing program to match the transactions with the backend ERP deposits table.

Background information -

Our previous credit card software, Payware Transact was discontinued and no longer supported. We decided to stay with Payware and upgrade to Payware Connect. Several years ago, I created an automated process that matched ERP deposits with Transact Request file and send me an Email with totals for each store and subject line telling me if in balance or not. Nothing worse than accountant coming to me two months later with a failed transaction and wanting to know why and to fix it.

The process has one flaw, it does not capture the Internet transactions processed through Authorize.net. At the time, I was working on store close out balancing and I was able to complete project since Internet was not the focus. Internet has their own outsourced IT resources not managed by me.

The process of upgrading the ERP software was brutal. I was at mercy of ERP vendor and not able to figure out what the resulting process is going to be. During the 18-month sales cycle, I was told we would have same capability as we currently have. So, I held my breath and took my lumps as they occurred.

As it turned out we did not get what we had and I had to change processes to fit what the ERP vendor wanted to code for. Settlement was by push and now set as a timer on Payware portal. I also discovered that the new programming marks the deposits records settled just because the transactions were sent. The transactions are not actually settled until PNC accepts and processes the transactions from Payware starting at 11:40pm. I brought this to the ERP vendor's attention and was told that "we have not seen any failure with this so we are not going to enhance programming". The API's exist to do this correctly but unfortunately I have limited Java experience and smart enough to know I do not want my fingerprints on Credit Card transaction processing code. There is more to the nightmare but of no value to mention here. Bottom line is new software is running and processing credit card transactions.

Since Payware Connect does not have a Request file holding status of processed credit card transactions I must change the process I created to balance with the ERP deposits table.

*Note - To complete the following process, I had to first set up Client Solutions to run from the IFS. See instructions later in this document.

New process outline –

1) When email is received, automatically extract attachment to folder on my desktop.

2) Unzip .XLSX from extracted attachment to IBMi IFS shared folder IACOUT.

3) Delete PNC upload.xlsx from IFS shared folder IACOUT.

4) Rename *.xlsx file in IFS shared folder IACOUT to PNC upload.xlsx.

5) Run Client Solutions extract to append PNC upload.xlsx to PNCTRAN table on IBMi.

6) Run program to generate spreadsheet and email.

New process details –

When email is received automatically, extract attachment to folder on my desktop:

I searched Google looking for email extractor programs. If I was handy with VB there are examples that I could have worked with.

After looking over a few programs available I settled on Outlook Email Extractor. It will work with Exchange Server, enables extraction to any folder, has subsequent processing option and non-recurring low price.

The software was easy to install and setup. I have been running trial version for a couple of weeks and works flawlessly.

Outlook Email Extractor –

Selecting existing profile and Edit displays profile setting dialog. Obviously if one does not exist you would select New.

General tab you give the Profile a name. E-mail Folder is where you specify what folder you want to watch.

Lots of filters to choose from, I am monitoring for sender name and subject.

Storage location, this is where it starts to get a little tricky. I am directing the output to a folder on my desktop and renaming to a generic name to so I can hardcode the batch with name of .zip to extract from. Files is received as Transaction Listing (9999999999).zip and I output as PNC daily upload.zip.

Example of email received –

Subsequent – this is where I tell the extractor to run batch file PNC IBMi upload.bat located in my Google Drive folder.

The last tab, Catch Up, is awesome. Allows me to run as many times as I need to test the process.

Batch file – PNC IBMi upload.bat

This batch program unzips the emailed zip file and launches IBMi Client Solutions command line processing to call a CLLE program on the IBMi. The CLLE program called is a wrapper that submits the actual program that transfers file from IFS and appends to SQL table on IBMi.

I attempted to adjust my PC path environment variable but was unsuccessful and decided to not get bogged down figuring out why and hard coded the path to executables as needed.

1) Change directory to 7-Zip.Extract PNC daily upload.zip (renamed from email earlier in the process)

2) Change directory to Client Solutions.To get around the password requirement you use PLUGIN=cfg

3) PLUGIN=logon, to logon with my credentials.

4) PLUGIN=rmtcmd, calls my submit program on IBMi.

5) PLUGIN=cfg /del, this will remove the configuration set in step four. This is for security purposes.

6)Pause, I put a pause in so the command window will hang telling me all has completed. I will remove this after a few weeks and when I am satisfied with the process.

PNC Transfer and Call Balancing program –

Submit program call by PLUGIN=rmtcmd.

Why JOBQ QS36EVOKE? The queue is multithreaded and not a production job queue. If something gets stuck I am notified and I can address. MSGW will not hold up production jobs if failure occurs. This type of job is ok to leave here for now.

Submit Client Solutions transfer and balancing program –

1) Delete existing object PNC upload.xlsx

2) Rename transferred spreadsheet to PNC upload.xlsx

3) Qshell java command to run command line transfer. (See set up Client Solutions in IFS later in this document.)

4)Run program to balance data to deposits and generate E-mail.

Balancing and email program –

This is a jewel and unfortunately, I do not have the time to go through it step by step. If you have any questions about any to the SQL code let me know.

I have since replaced CEELOCT with my own date service program. I am not converting this program to use the service program as it is a good example of CEELOCT API.

I do push the limits of CLLE. New RUNSQL command is awesome.

/*-------------------------------------------------------------------*/

/* Create and send via Email yesterday First Data Settlements */

/*-------------------------------------------------------------------*/

PGM

DCL VAR(&QTIME) TYPE(*CHAR) LEN(6)

DCL VAR(&RPTDT) TYPE(*CHAR) LEN(6)

DCL VAR(&SQL) TYPE(*CHAR) LEN(500)

DCL VAR(&IFS) TYPE(*CHAR) LEN(100) +

VALUE('\\qcapibmi\excel\')

DCL VAR(&USER) TYPE(*CHAR) LEN(10)

DCL VAR(&VIEWAS) TYPE(*CHAR) LEN(02) VALUE('01') /* +

Excel */

DCL VAR(&SPLF) TYPE(*CHAR) LEN(10) VALUE(SETTLED) /* +

Program name */

DCL VAR(&FILE) TYPE(*CHAR) LEN(60) VALUE('Yesterday +

settled amounts')

DCL VAR(&FILEF) TYPE(*CHAR) LEN(60) VALUE('Yesterday +

settled amounts')

DCL VAR(&FILENAME) TYPE(*CHAR) LEN(100)

DCL VAR(&PATHEXL) TYPE(*CHAR) LEN(100) VALUE('/excel/')

DCL VAR(&EXLMSG) TYPE(*CHAR) LEN(100)

DCL VAR(&EXLH_DT) TYPE(*CHAR) LEN(10)

DCL VAR(&SETL_DT) TYPE(*CHAR) LEN(10)

DCL VAR(&SETL) TYPE(*CHAR) LEN(9) VALUE('SETTLED:%')

DCL VAR(&MSG_STAT) TYPE(*CHAR) LEN(100)

/* Date routine variables ----------------------------------------------*/

DCL VAR(&YESTERDAY) TYPE(*DEC) LEN(8 0)

DCL VAR(&LILIAN) TYPE(*CHAR) LEN(4)

DCL VAR(&JUNK1) TYPE(*CHAR) LEN(8)

DCL VAR(&JUNK2) TYPE(*CHAR) LEN(23)

DCL VAR(&WDATE) TYPE(*CHAR) LEN(8)

DCLF FILE(SETTBALA) OPNID(SETTBAL)

/* Get local time from system: When this call is */

/* complete, &LILIAN will contain the number of */

/* days between today and Oct 14, 1582. */

CALLPRC PRC(CEELOCT) PARM(&LILIAN &JUNK1 &JUNK2 *OMIT)

/* Subtracting 1 from &LILIAN will produce yesterday's date */

CHGVAR VAR(%BIN(&LILIAN)) VALUE(%BIN(&LILIAN) - 1)

/* Convert Lillian to yyymmdd date */

CALLPRC PRC(CEEDATE) PARM(&LILIAN 'YYYYMMDD' &WDATE *OMIT)

CHGVAR VAR(&YESTERDAY) VALUE(&WDATE)

/* End date routine --------------------------------------------------*/

CHGVAR VAR(&SETL_DT) VALUE(%SST(&WDATE 1 4) *CAT '-' *CAT +

%SST(&WDATE 5 2) *CAT '-' *CAT %SST(&WDATE 7 2))

CHGVAR VAR(&EXLH_DT) VALUE(%SST(&WDATE 5 2) *CAT '/' *CAT +

%SST(&WDATE 7 2) *CAT '/' *CAT %SST(&WDATE 1 4))

CHGVAR VAR(&EXLMSG) VALUE(&EXLH_DT *BCAT 'First Data +

Settlements')

RTVJOBA USER(&USER) DATE(&RPTDT)

/* ERP program to retrieve file name for this run */

CALL PGM(XAFILENAME) PARM(&VIEWAS &SPLF &FILE &FILEF)

CHGVAR VAR(&FILENAME) VALUE(&PATHEXL *TCAT &USER *TCAT +

'/' *TCAT %SST(&FILE 1 29))

/* Create table to hold summarized transaction dollars from uploaded data */

RUNSQL SQL('create table r50modsdta.pncssum as (select f2 +

as rzclid, f5 as settle_date, +

cast(ifnull(sum(case when f1 = ''Refund'' then +

f12 end),0) as decimal(11,2)) as crdt_crdt_amt, +

cast(ifnull(sum(case when f1 = ''Sale'' then f12 +

end),0) as decimal(11,2)) as crdt_sale_amt from +

r50modsdta.pnctrans where f5 = ''' *CAT &SETL_DT +

*CAT ''' group by f2,f5 order by f2) with DATA') +

COMMIT(*NONE) NAMING(*SQL)

/* Create table to output in email based on summarized transaction dollars */

RUNSQL SQL('create table r50modsdta.sett_ord as (select +

owstr, owname, rzclid, settle_date, +

ifnull(dec(crdt_sale_amt,11,2),0) as +

debit_sale,ifnull(dec(crdt_crdt_amt,11,2),0) as +

credit_sale, ifnull(dec(crdt_sale_amt,11,2),0) - +

ifnull(abs(dec(crdt_crdt_amt,11,2)),0) as +

settle_net from r50files.vcostore inner join +

r50modsdta.rtccxref on cxvaiid = owmrch left +

outer join r50modsdta.pncssum on rzclid = +

cxpncid and settle_date = ''' *CAT &SETL_DT *CAT +

''' where owdel = ''A'') WITH DATA') +

COMMIT(*NONE) NAMING(*SQL)

/* Add ERP deposits net column to output table */

RUNSQL SQL('alter table r50modsdta.sett_ord add column +

deposit_net dec(11,2) default 0 not null') +

COMMIT(*NONE) NAMING(*SQL)

/* Add ERP deposits debit column to output table */

RUNSQL SQL('alter table r50modsdta.sett_ord add column +

deposit_debit dec(11,2) default 0 not null') +

COMMIT(*NONE) NAMING(*SQL)

/* Add ERP deposits credit column to output table */

RUNSQL SQL('alter table r50modsdta.sett_ord add column +

deposit_credit dec(11,2) default 0 not null') +

COMMIT(*NONE) NAMING(*SQL)

/* Update output table with ERP summarized debits */

RUNSQL SQL('update r50modsdta.sett_ord a set +

a.deposit_debit = ifnull((select cast(sum(tdamt) +

as dec(11,2)) as debit from r50files.vardeps b +

inner join r50files.vcohead on oaord = b.tdord +

and oabocd = b.tdbocd where a.owstr = oastr and +

b.tddate = ''' *CAT &WDATE *CAT ''' and +

(b.tdpdby = ''A'' or b.tdpdby = ''D'' or +

b.tdpdby = ''M'' or b.tdpdby = ''V'' or tdpdby = +

''WA'' or tdpdby = ''WD'' or tdpdby = ''WM'' or +

tdpdby = ''WV'') and b.tdaut# <> ''B/O***'' and +

b.tddc = ''D'' group by oastr),0)') +

COMMIT(*NONE) NAMING(*SQL)

/* Update output table with ERP summarized credits */

RUNSQL SQL('update r50modsdta.sett_ord a set +

a.deposit_credit = ifnull((select +

cast(sum(tdamt) as dec(11,2)) as debit from +

r50files.vardeps b inner join r50files.vcohead +

on oaord = b.tdord and oabocd = b.tdbocd where +

a.owstr = oastr and b.tddate = ''' *CAT &WDATE +

*CAT ''' and (b.tdpdby = ''A'' or b.tdpdby = +

''D'' or b.tdpdby = ''M'' or b.tdpdby = ''V'' or +

tdpdby = ''WA'' or tdpdby = ''WD'' or tdpdby = +

''WM'' or tdpdby = ''WV'') and b.tdaut# <> +

''B/O***'' and b.tddc = ''C'' group by +

oastr),0)') COMMIT(*NONE) NAMING(*SQL)

/* Update output table with ERP summarized net of credits and debits */

RUNSQL SQL('update r50modsdta.sett_ord a set +

a.deposit_net = ifnull((select cast(sum(tdamt) +

as dec(11,2)) as debit from r50files.vardeps b +

inner join r50files.vcohead on oaord = b.tdord +

and oabocd = b.tdbocd where a.owstr = oastr and +

b.tddate = ''' *CAT &WDATE *CAT ''' and +

(b.tdpdby = ''A'' or b.tdpdby = ''D'' or +

b.tdpdby = ''M'' or b.tdpdby = ''V'' or tdpdby = +

''WA'' or tdpdby = ''WD'' or tdpdby = ''WM'' or +

tdpdby = ''WV'') and b.tdaut# <> ''B/O***'' and +

b.tddc = ''D'' group by oastr),0) - +

ifnull((select cast(sum(tdamt) as dec(11,2)) as +

debit from r50files.vardeps c inner join +

r50files.vcohead on oaord = c.tdord and oabocd = +

c.tdbocd where a.owstr = oastr and c.tddate = +

''' *CAT &WDATE *CAT ''' and (c.tdpdby = ''A'' +

or c.tdpdby = ''D'' or c.tdpdby = ''M'' or +

c.tdpdby = ''V'' or tdpdby = ''WA'' or tdpdby = +

''WD'' or tdpdby = ''WM'' or tdpdby = ''WV'') +

and c.tdaut# <> ''B/O***'' and c.tddc = ''C'' +

group by oastr),0)') COMMIT(*NONE) NAMING(*SQL)

/* Create temp table used to check if in balance */

RUNSQL SQL('Drop Table qtemp.settbala') COMMIT(*NONE)

MONMSG MSGID(SQL9010)

RUNSQL SQL('create table qtemp.settbala as (select +

dec(sum(settle_net),11,2) - +

dec(sum(deposit_net),11,2) as balance from +

r50modsdta.sett_ord) with data') COMMIT(*NONE) +

NAMING(*SQL)

/* Linoma Suveyor export table to IFS as Excel workbook */

SURVEYOR/EXPDTA USEQRY(*NO) INFILE(R50MODSDTA/SETT_ORD) +

TYPEOFEXP(*EXCEL) XLSTITLEYN(*YES) +

XLSTITLE(&EXLMSG) XLSTBOLD(*YES) +

HEADTYPE(*HEADINGS) XLSHSTROW(4) XLSBOLD(*NO) +

XLSHUNDERL(*YES) XLSHBCOLOR('*LIGHT_GRAY') +

XLSAUTOSIZ(*YES) XLSSHEET(RTSETTDCL) +

IFSDEST(&FILENAME) OUTPUT(*NONE)

/* Set up subject line - .10 threshold */

RCVF OPNID(SETTBAL)

IF COND((&SETTBAL_BALANCE *LE .10) *AND +

(&SETTBAL_BALANCE *GE -.10)) THEN(DO)

CHGVAR VAR(&MSG_STAT) VALUE('Yesterday PNC Settlement +

is in balance! Have a great day!')

CHGVAR VAR(&MSG_STAT) VALUE(%trim(&MSG_STAT))

SNDMAIL RECIPIENT((RICHARD xxxxxxx@xxxxxx.com) (JASON +

xxxxxxxxx@xxxxx.COM)) SENDER(QCAPIBMI +

xxxxxx@xxx.com) SUBJECT(&MSG_STAT) +

MESSAGE(&EXLMSG) ATTACHMENT(&FILENAME)

ENDDO

ELSE DO

CHGVAR VAR(&MSG_STAT) VALUE('Yesterday PNC Settlement, +

one or more stores are OUT OF BALANCE!')

CHGVAR VAR(&MSG_STAT) VALUE(%trim(&MSG_STAT))

SNDMAIL RECIPIENT((RICHARD xxxx@xxxx.com) (JASON +

xxxxxxxxx@xxxxx.COM) (HELPDESK +

HELPDESK@1800LIGHTING.COM)) SENDER(QCAPIBMI +

xxxxxx@xxx.com) SUBJECT(&MSG_STAT) +

MESSAGE(&EXLMSG) ATTACHMENT(&FILENAME)

ENDDO

/* Delete the temp tables */

DLTF FILE(r50modsdta/SETT_ORD)

DLTF FILE(r50modsdta/PNCSSUM)

END:

Running IBMi Client solutions from IFS.

While searching with Google I found this PDF by Craig Pelkie. He does an awesome job of showing us how to set up Client Solutions to run from IFS.

Complete Instructions –

http://www.web400.com/downloads/ACS/RunIBMiAccessClientDataXfers.pdf

The email I receive contains the excel spreadsheet created during the balancing process. I removed the data for this example. Basically, if out of balance subject is received I look and Column G and H looking for the mismatch. Then the real fun begins.

Ran automatically this morning without issue.

Have your best day!

~Richard

Stumped, SQLRPGLE nomain module with data structure

2016-02-17T15:19:00.000-05:00

I am stumped and have exhausted all clues provided by searching with Google high and low. My current challenge is a big one. Create an SQL nomain module, copybook member, service program that will enable to manage my backend ERP date fields that are eight digit numeric and not true date field.

I do a lot of CLLE to automate the ERP reports which usually require date ranges. I also have the need to create CLLE programs that can do some date math.

I currently use RDI 9.5.0.2 exclusively and IBMi OS 7.1. I could do all of this with CEEDATE and play with Lillian but where the fun in that?

All of this could be resolved by the good folks at IBM by giving us the option to use SQL Select or Set to populate a CLLE variable. ;-)

I successfully figured out how to create and compile SQLRPGLE Nomain module. The trick was the compile type option OBJTYPE, this needs to be *module when using the CRTSQLRPGI. The other stumbling point is RPGPOPT type needs to be set to *LVL1 if Data Structure is in your Prototype member.

This is where I am failing, I can make it work until got to the data structure. I am being a little hard headed and I could just fall back to what works passing one parameter per procedure call. But I believe I need to learn this technique for future use.

I managed to create module, prototype, service program, binding directory. I learned I could add DCLPRCOPT to my CLLE to set the binding directory.

So my main CLLE program DYSLSRPTC that needs dates looks like this,

DCLPRCOPT DFTACTGRP(*NO) ACTGRP(*CALLER) BNDDIR(R50MODS/CAPITOL)

CALLPRC PRC(GETPRVQTR) PARM(' ') RTNVAL(&RTNQTR)

CALLPRC PRC(GETTODAY) PARM(' ') RTNVAL(&TODAY)

I have GETTODAY working. I believe I can enhance this with *nopass and eliminate the PARM. I do not need to send the procedure data, just need to get back today's date.

My problem arose when I needed to get last quarter beginning and ending dates. I just want to call the procedure and have it return the two dates in one 16 alpha field as yyyymmddyyyymmdd. This seems like a perfect use for prototyping with data structure and pass back as one field.

I have tried many different permeation's with no luck. Currently getting pointer not referenced when debugging. Here is my current prototype source used for /copy.

*--------------------------------------------------------------------

* Get previous beginning and ending quarter date yyyymmdd

*---------------------------------------------------------------------

dcl-pr getPrvqtr char(16);

*n likeds(quarterdt);

end-pr;

dcl-ds quarterdt qualified;

lbegqtr char(8);

lendqtr char(8);

End-Ds;

Here is my procedure in my nomain module,

//****************************************************

// getPrvqtr returns previous beginning and ending quarter yyyymmdd

dcl-proc getPrvqtr export;

dcl-pi *n char(16);

qtrdt likeds(quarterdt);

End-Pi;

dcl-s last_quarter int(10);

dcl-s current_quarter int(10);

dcl-s medate char(8);

Exec SQL Set :medate = replace(char((current_date -

day(current_date) days), iso), '-', '');

// Last beginning and ending quarter

Exec SQL Set :current_quarter = quarter(current_date);

if current_quarter = 1;

last_quarter = 4;

else;

last_quarter = current_quarter - 1;

EndIf;

Exec SQL Set :quarterdt.lbegqtr =

case :last_quarter

when 1 then substring(:medate,1,4) concat '0101'

when 2 then substring(:medate,1,4) concat '0401'

when 3 then substring(:medate,1,4) concat '0701'

when 4 then substring(:medate,1,4) concat '1001'

end;

Exec SQL Set :quarterdt.lendqtr =

case :last_quarter

when 1 then substring(:medate,1,4) concat '0331'

when 2 then substring(:medate,1,4) concat '0630'

when 3 then substring(:medate,1,4) concat '0930'

when 4 then substring(:medate,1,4) concat '1231'

end;

return quarterdt;

End-Proc;

Any assistance with this will be greatly appreciated.

Thanks,

Richard

DB2 IBM i SQLRPGLE Free and Date conversion

2016-01-30T12:36:00.000-05:00

One annoying capability missing from RUNSQL in CLLE is the ability to use Select into a variable. Sure you can create temporary file with create table as and use RCVF to retrieve into our variable but that can be a pain if QTEMP is not where I believe it should be. The QTEMP story is for another day.

I am currently automating month end and daily sales reporting and wanted a better way than CEEDATE and wanted to use SQL. I decided to create a little SQLRPGLE program, when called, would return dates based on current date.

My CLLE programs need last month end date, last beginning quarter, last ending quarter, yesterday, and last month name for report selection criteria.

I took the opportunity to code completely in SQLRPGLE Free. This is my first totally Free program.

There is some default code that I copy from program to program and not used. I wanted to have examples of Free code for future programs.

Feel free (no pun intended) to critic my code. I never claimed to be a programmer but for some reason find myself programming. Go figure.

ctl-opt option(*nodebugio) dftactgrp(*no) bnddir('QC2LE');
//********************** M O D I F I C A T I O N S *************************
   // **
// Jacksonville, Florida 32223 **
// **
// This program returns last month end date, last month name, yesterday,**
// last beginning quarter and last ending quarter. **
// **
// **
// **
// **
// **
//******************** M O D I F I C A T I O N S *************************
// Date Programmer Description **
// ---------- ------------ -------------------------------------------- **
// 01/11/16 Richard Bryant **
// **
// **
// **
//*************************************************************************
// ------------------------------------- Prototypes
dcl-pr rtvdate ExtPgm;
*n char(8);
*n char(10);
*n char(9);
*n char(8);
*n char(8);
*n char(8);
End-Pr;

dcl-pi *n;
medate char(8); // YYYYMMDD Prior month end date
medatef char(10); // YYYY-MM-DD Prior month end date
mename char(9); // Month name Prior month end name
lbegqtr char(8); // YYYYMMDD last beginning quarter date
lendqtr char(8); // YYYYMMDD last ending quarter date
yesterday char(8); // YYYYMMDD yesterday
End-Pi;

// Program Status Data Structure
dcl-ds PgmDs PSDS;
pgmnam *PROC;
prmnbr *PARMS;
job char(10) pos(244);
user char(10) pos(254);
End-Ds;

// Data Structures

// Program Constants
dcl-c crlf x'0d25'; // Carridge return / Line feed
dcl-c s ' '; // Space
dcl-c d '-'; // Dash
dcl-c @Apostr x'7d'; // '
dcl-c @Dblqt x'7f'; // "
dcl-c @Gt x'6e'; // >
dcl-c @slgt x'616e'; // />

// Date conversion fields
dcl-s @numA zoned(6:0) inz(041205);
dcl-s @dateA date(*YMD) inz(D'2004-12-04');

// Standalone Variables
dcl-s fd int(10);
dcl-s $xml char(512);

dcl-s @CmdStr char(512) inz;
dcl-s message char(52) inz;
*
dcl-s sdays zoned(3:0);
dcl-s ts timestamp;
dcl-s date date;
dcl-s sdate date; // Sales date
dcl-s currdt zoned(8:0);
dcl-s currtm zoned(6:0);
dcl-s current_quarter int(10);
dcl-s last_quarter int(10);

//************************************************************************
// MainLine
//

// Set SQL options
Exec Sql Set Option Datfmt=*Iso, Commit=*None, Closqlcsr=*Endmod;

ts = %timestamp(); // Get current date and time
date = %date(ts); // Load current date field
currdt = %dec(%date: *ISO); // Current date yyyymmdd
currtm = %dec(%time: *HMS); // Currrent time

// Formated last month end character date YYYY_MM_DD
Exec SQL Set :medatef = replace(char((current_date + 1 Month - DayofMonth
(current_date + 1 Month) Days - 1 month), iso), '-', '_');

// Last month character date YYYYMMDD
Exec SQL Set :medate = replace(char((current_date + 1 Month - DayOfMonth
(current_date + 1 Month) Days - 1 month), iso), '-', '');

// Last month name
Exec SQL Set :mename = monthname(current_date + 1 Month - DayOfMonth
(current_date + 1 Month) Days - 1 month);

// Yesterday YYYYMMDD
Exec SQL Set :yesterday = replace(char((current_date - 1 day),
iso), '-', '');

// Last beginning and ending quarter
Exec SQL Set :current_quarter = quarter(current_date);

if current_quarter = 1;
last_quarter = 4;
else;
last_quarter = current_quarter - 1;
EndIf;

Exec SQL Set :lbegqtr =
case :last_quarter
when 1 then substring(:medate,1,4) concat '0101'
when 2 then substring(:medate,1,4) concat '0401'
when 3 then substring(:medate,1,4) concat '0701'
when 4 then substring(:medate,1,4) concat '1001'
end;

Exec SQL Set :lendqtr =
case :last_quarter
when 1 then substring(:medate,1,4) concat '0331'
when 2 then substring(:medate,1,4) concat '0630'
when 3 then substring(:medate,1,4) concat '0930'
when 4 then substring(:medate,1,4) concat '1231'
end;

*inlr = *on ;

2/1/16 - Whoops, my SQL is flawed. Today, 2/1/16, returned previous month end 1/29/16. I found a blog written by Sam Lennon, programmer who opened my eyes to SQL, to figure out where I went wrong. Here is the corrected code...

// Formated last month end character date YYYY_MM_DD
Exec SQL Set :medatef = replace(char((current_date -
day(current_date) days), iso), '-', '_');

// Last month character date YYYYMMDD
Exec SQL Set :medate = replace(char((current_date -
day(current_date) days), iso), '-', '');

// Last month name
Exec SQL Set :mename = monthname(current_date -
day(current_date) days);

// Yesterday YYYYMMDD
Exec SQL Set :yesterday = replace(char((current_date - 1 day),
iso), '-', '');

// Last beginning and ending quarter
Exec SQL Set :current_quarter = quarter(current_date);

if current_quarter = 1;
last_quarter = 4;
else;
last_quarter = current_quarter - 1;
EndIf;

Exec SQL Set :lbegqtr =
case :last_quarter
when 1 then substring(:medate,1,4) concat '0101'
when 2 then substring(:medate,1,4) concat '0401'
when 3 then substring(:medate,1,4) concat '0701'
when 4 then substring(:medate,1,4) concat '1001'
end;

Exec SQL Set :lendqtr =
case :last_quarter
when 1 then substring(:medate,1,4) concat '0331'
when 2 then substring(:medate,1,4) concat '0630'
when 3 then substring(:medate,1,4) concat '0930'
when 4 then substring(:medate,1,4) concat '1231'
end;

~Richard

“f u cn rd ths, u cn gt a gd jb n sftwr tstng.” (Anonymous)

IBM i Access Client Solutions, AWESOME!

2016-01-29T15:03:00.000-05:00

I have been running Client Solutions since October and have found it is now a full replacement for Iseries Access and Ops Navigator. The last update added the last missing piece, Run SQL scripts.

For the most part I use Linoma's Surveyor/400 software which is awesome and I really miss it when I have to work on machine it is not licensed on. That's where Client Solutions comes to play, and play well, it now does.

I have to say even over remote VPN client it all works very well. I am starting to use Navigator i for all monitoring replacing green screen.

One gotcha to be aware of. I went to delete 125mb save file from IFS folder and the machine ground to a halt. Not a good thing in the middle of the day. Had to sweat it out for 20 minutes before the machine would respond.

I knew there was a December update that I had not loaded so I downloaded and installed. Install was painless and now have run SQL option.

Planning to wait until off hours on Sunday to try deleting from IFS and see if issue was resolved in last update.

Have a great day!

~Richard

SQL is cool!

2015-08-28T08:16:00.001-04:00

SQL is cool!

~ Richard

I really hate this darn machine;
I wish that they would sell it.
It won't do what I want it to,
but only what I tell it.
~Author Unknown

Web service call from SQLRPGLE

2014-12-17T08:01:00.001-05:00

Yesterdays challenge is to replace a scheduled timed event with a dynamic process. Give's us an opportunity to try REST from RPGLE using new SYSTOOLS HTTPGETCLOB.

The program is simple and just prompts the Web programming to capture funds and invoice order in ERP backend. Responds with OK and if not send me an E-mail.

Very cool and once I managed to get all the special chatters in the right place works great. This can also be done in a CLLE with RUNSQL command and CREATE TABLE AS. I initially blew it in CLLE and reported to IBM could not get it working. They came back a few days later, right after I got RPGLE version working, and informed me that '+' continuation line character had a space before and that was causing SQL to fail with SQLSTT 38000. DUH!

The next step is a SOAP message so we can trade data between web and back end ERP.

I wish I had the time to learn PowerRuby. I believe I can do this with Ruby as well.

My next endeavor is JSON to link back end VAI ERP to DESK.COM. Again I think PowerRuby can help and really need to figure out how to get there.

//******************Begin sub-routines*******************************

//********************************************************************

// Run web service and check response

//********************************************************************

begsr xxxx;

// Change Job CCSID to 37

CmdStr = 'CHGJOB CCSID(37)';

callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

// Call Web Service Action=doInvoicing

Exec SQL

SELECT data into :m_data FROM

(VALUES(SYSTOOLS.HTTPGETCLOB

('http://xxxxxx.xxxxxxxx.com/xxx/+

xxxxxxx.cfm?action=doinvoicing',+

'<httpHeader connectTimeout="10000"> +

</httpHeader>'))) WS(data);

// Change Job CCSID to system default

CmdStr = 'CHGJOB CCSID(65535)';

callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

// If Web service response not OK - send Email

if m_data <> 'OK';

exsr email_err;

EndIf;

endsr;

//********************************************************************

// Send email if failed to get response from Web

//********************************************************************

begsr email_err;

// Build send email command

CmdStr = 'SNDMAIL RECIPIENT((RICHARD xxxxxxxx@xxxxxxxx.COM))';

CmdStr = %trim(CmdStr) + ' SENDER(QCAPIBMI xxxxxx@xxxxxxxxx.COM)';

CmdStr = %trim(CmdStr) + ' SUBJECT(' + Quote + 'PO Receipt processed';

CmdStr = %trim(CmdStr) + ' ***Web Capture funds not processed ***';

CmdStr = %trim(CmdStr) + Quote + ') MESSAGE(' + Quote;

CmdStr = %trim(CmdStr) + 'EDIARDSCF failed to receive response OK';

CmdStr = %trim(CmdStr) + ' *** Contact WEB ***';

CmdStr = %trim(CmdStr) + Quote + ')';

// Send email

callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

EndSr;

~Richard

One man's crappy software is another man's full time job. ~Jessica Gaston, 2008

Microsoft Virtual PC..Oh what fun it is to ride...

2014-12-08T07:38:00.001-05:00

My current challenge is connecting to a customers Sonicwall with GVC client. I already have one connection using GVC. I have been supplied with full GVC installer and it's a couple of versions behind what I currently have installed. Rather than take a chance of corrupting my current setup I find it's time to set up a virtual machine.

I started with MS Virtual PC and it has been a real challenge to get running. In the end the challenge was my lack of knowledge on what OS is supported in MS Virtual PC.

I downloaded MS Virtual PC and install went easy enough but from there the road became bumpy. My PC is running Windows 7 Home Premium and I am suspicious is not playing with MS Virtual PC very well. I finally found an article listing compatibility and even though shows yes the foot note states no integration with host. The interface was choppy with video issues and response time not good.

Now my direction is changed to "How to upgrade MS Home Premium to Windows 7 Pro?". I found Windows Anytime Upgrade web page and followed my nose from there.

My host PC upgraded flawlessly in about 15 minutes. $89.00 poorer, four hours of research and I am now running Windows 7 Pro on desktop.

I then turned my attention to VM I set up based on Home Premium. Like a dummy I deleted the VM file when I found was not going to fully work. I found the deleted VM in recycle bin and restored with one mouse click.

The real pain in setting up VM is all the MS updates that have to be applied. My host machine is up to date so did not need MS updates installed. The guest is a different story. I built it from my original Home Premium DVD. It took a couple of hours and several reboots before it was up to date. Honestly not sure how long since I let go by itself periodically visiting PC during the day while I did some household chores.

At this point I am not sure how Windows Anytime Upgrade will work with a VM. One of the initial options is "do you have key", yes I do and I pumped it in and installer took off. After about 15 minutes VM rebooted and displayed "Failed to install upgrade". Bummer.

Thought about it for a minute and stated don't you know who I am? I started the process over and this time I got ......

This is bad to the bone. I have access to host PC drives through integration feature which is now working flawlessly. So far I have installed Chrome, IBMi Access and Sonic Wall GVC. Connected to client site with no problem!

And now for my next trick....install and upgrade PTF's for V6R1 IBMi model 550 with three partitions via HMC remotely.

Have you best day!

~Richard

Success seems to be largely a matter of hanging on after others have let go. ~William Feather

As the wind blows...

2014-12-03T21:27:00.002-05:00

My PowerRuby ambitions are forced to take a back seat for a while. Work that pays is new focus with planning for IBM I5/550 OS PTF update on three partitions and iTERA HA. Includes HMC update as well.

Also planning for new IBMi Power8 install and migration from Power6 box. The old Power6 running since 2007 only had a few problems resulting limited down time. Battery cache and one drive went bad but we never missed a beat. An unused controller card failed last week and we decided to just vary it off. Ran 24/7 with automatic IPL monthly and limited operator intervention (me). I almost finished converting nightly tape to the cloud. Need just a wee bit more disk since I have no dedup software. The new box more that doubles disk. Got to love it.

Hopefully get back to PowerRuby after x-mas. It's all loaded and ready to go. I need to update with latest PTF cumulative and groups for this box.

My first challenge is adding VPN connection to my already crowded PC. Thank fully it's Sonic Wall and unfortunately I currently only have access the the full install client with new connection parameters embedded. I do not want to take a chance and install two on same machine/OS.

Time for MS Virtual PC. Downloaded from MS and ran install and get to 93%, BAM, "Installed failed reverting configuration". I am getting 0x80004005 error code. Can not have more than 13 interfaces, who knew? I dig into regedit and find one of the interfaces is Shrewsoft. Ha, don't need that. Uninstalled Shrewsoft and checked registry entry and find it's gone and now there are 12 interfaces.

Run MS .msi and SUCCESS! It's been a long day I'll figure out how to use it tomorrow night.

~Richard

I may not be there yet, but I'm closer than I was yesterday. ~Author Unknown

PowerRuby....installation

2014-12-01T07:45:00.000-05:00

Over Thanksgiving weekend, while wife not watching, I downloaded PowerRuby to PC. Took a while for little over 200mb to download. Took even longer to upload to IBMi.

I discovered that IBM 5733SC1 - IBM Portable Utilities for i5/OS was not installed. Lucky for me complete OS image is stored in IFS, it's nice to have plenty of disk.

I really like Virtual optical drive and image catalogs. Here is a good article explaining image catalogs and how to setup and use. Peace of cake -

WRKIMGCLG on command line....

Select option 12, Work with Entries....

Once DVD-ISO image is mounted select F12 and return back to work with Image Catalogs..

All you need to do now is option 8 Load.

Virtual Optical drive OPTVRT01 is now ready to use to restore licensed program.

THE IBM* PORTABLE UTILITIES FOR i5/OS* consists of a *BASE and Option 1. Both must be installed with CL commands similar to these:

RSTLICPGM LICPGM(5733SC1) DEV(OPTxx) OPTION(*BASE) RSTOBJ(*ALL) LNG(2924)

RSTLICPGM LICPGM(5733SC1) DEV(OPTxx) OPTION(1)RSTOBJ(*PGM)

No issues, RSTLICPGM, like other steps took longer than expected. I think all told about 15 minutes.

I do need to go and get all the IBM PTF's to complete the install but that will have to wait till this coming weekend. I run full PTF update twice a year and am overdue for second update. Major rule I have is not to install PTF's on a month end weekend. Been there and got bite on ass to prove it.

Oh yeah, this is about PowerRuby.....

At this point I uploaded to IBMi following PowerRuby instuctions.

No issues and went smoothly. I now have PowerRuby installed and ready for my first new rails app from scratch....

Unfortunately will have to wait. I have a bunch of side work including replacing daily tape backup with save to cloud (Carbonite) and possible V5R4 upgrade.

~Richard

When you catch bugs early, you also get fewer compound bugs. Compound bugs are two separate bugs that interact: you trip going downstairs, and when you reach for the handrail it comes off in your hand. ~Paul Graham, "The Other Road Ahead," 2001

Ruby on Rails VS ColdFusion - Commercial #8 of 9

2014-11-28T11:05:00.001-05:00

I'm headed down the Rails. Let's see how far I can go with IBMi and PowerRuby.

~Richard

Submit Job Schedule Entries Immediately

2014-11-23T09:35:00.000-05:00

The AS/400 Job Scheduler will submit to job queue specified in Job Scheduled entry unless you manually submit with option 10. I found this link Submit Job Scheduled Entries that looks interesting.

Not something I need since it is easy to work around by manually submitting the job as I want with SBMJOB. Normally I handle Job Scheduler and many users are unaware. In the case where users need to be able to submit on demand this may be very useful.

Have your best day!

~Richard

Accessing Data on Microsoft SQL Server From IBM i

2014-09-18T16:12:00.000-04:00

My latest challenge is how to marry data on our Ecommerce SQL servers with our IBMi DB2 data from IBMi system using SQL. We managed to setup a linked server on Ecommerce side of the fence which allows us to access data from both systems at same time, very cool.

I would love to be able to do this from the IBMi and I found this IBMi tech document. Accessing Data on Microsoft SQL Server From IBM i

I am looking for someone who may have used this software and give me some insight on how well it has worked or not worked. Does anyone have any experience with this software?

I requested a quote from our IBM business partner and first response was software does not run on IBMi. I am having the business partner re-query IBM since that is not what tech document is leading me to believe. Maybe the tech doc is flawed.

My hope is this capability would allow me to write SQLRPGLE programs on IBMi that can insert or update tables directly on the SQL servers. I would be bringing in EDI data into IBMi and updating tracking information on SQL servers that feed information to our E-commerce customers. VERY, VERY COOL!

~Richard

It's the only job I can think of where I get to be both an engineer and an artist. There's an incredible, rigorous, technical element to it, which I like because you have to do very precise thinking. On the other hand, it has a wildly creative side where the boundaries of imagination are the only real limitation. ~Andy Hertzfeld, about programming

IBM i transfer Convert UNICODE to CHAR

2014-07-26T07:31:00.001-04:00

For the past couple of years I have noticed when transferring a file to IBM i from Excel the default field type is UNICODE. That caused issues with data after transfer so I have been manually changing them during file transfer definition process to CHARACTER.

The spreadsheets had few fields so not much of a pain and I did not look for a reason why this is happening. I am now working with a spreadsheet that has 38 columns to be transferred and changing from UNICODE to CHARACTER has become a pain.

Fifteen minutes of Google search and problem solved. With Google the answers are always only a few keystrokes away!

Microsoft Excel Data Transfer Add-Ins: Convert UNICODE to CHAR

The command did return something about failed but it did just what was needed. Not sure what the failed message is for.

When the world says, "Give up,"
Hope whispers, "Try it one more time."
~Author Unknown

Have your best day!

~Richard

Whoohoo!! Factory released KitKat 4.4.2 is installed and rooted!!!!!

2014-05-20T07:17:00.000-04:00

Finally after many months of waiting and not really knowing if Samsung would update my beloved Galaxy Note 8” 3G GT-N5100 phone.

Now I'm rocking 4.4.2, yeah baby!!

A simple process I followed at International Business Times ran smooth. I had a number of apps that did not update and received many failed to load messages when I restarted. All were fixed by uninstalling and reloading from play store. All except Amazon Kindle, of course I am deep into a book I would like to finish. I have not found a fix as of yet but will chase it down this evening.

Also ran root as well from here.

EDI Invoicing

2014-02-20T07:47:00.000-05:00

Another tough day ahead with EDI Invoicing. When the going gets tough the tough get going. We will overcome!

Welcome Aboard!

2014-01-13T08:37:00.000-05:00

Today most excellent RPGLE programmer and good friend Rick Santiago starts as Senior Programmer/Analyst at Capitol Lighting.

Rick and I first met at GE Aircraft Engines back in 1996. Since then I have sub-contracted Rick to do my "heavy lifting" RPGLE programming at every job I have worked at. Every single one was a huge success.

At Pylon Manufacturing we created awesome "Pick to Pallet" process and modified BPCS order ingestion program to incorporate Extol EDI to automate inbound processing.

At Quipp Systems we created Payroll time tracking and really wild commissions program utilizing MAPICS.

At NanjingUSA Rick assisted in several projects which included implementation of S2K ERP and automated EDI process.

The next five years are going to be really exciting. We now work together at Capitol Lighting and sky is the limit. Capitol is dedicated to the IBMi and is looking to us to help integrate technology into operations as Capitol grows to 100 million dollar sales mark. This is a terrific challenge and opportunity for us and Capitol Lighting.

We are IBMi, watch us grow!

We're not in Kansas anymore....

2013-08-06T09:00:00.000-04:00

Web Services, SOA, SOAP, HTTP, C++, WSDL, DISCO, Lions, Tigers and Bears. I’m used to diving into the deep end of the pool but after two days I think I have fallen into the Marianas Trench still waiting to hit bottom.

My current challenge is to send Name, Order and Email for new orders to LISTRAK Email Marketing Services utilizing API and SOAP formatted XML from our IBMi.

A little lite reading.....

developerWorks SOA and web services

Listrak integration services

Consuming Web services from RPG or COBOL programs on System i

~Richard

If we are facing in the right direction, all we have to do is keep on walking. ~Buddhist Saying

Whooohooo! IBMi V7R1 installed and working with no problems.

2013-05-27T07:55:00.000-04:00

Good to be back and up to date, was at V5R4 and I was missing some of the enhancements that were in V6R1. I upgraded several machines three years ago to V6R1 and did not like going back and working with V5R4.

Since I am now essentially a one man IT shop (software/IBMi) and buried deep in fixing and supporting a failed ERP implementation I outsourced the upgrade to Source Data Products. Over the years I have done about 15 installs/upgrades but has never been my focus and it takes planning to do it right. I did not have the time to plan and do the upgrade myself.

Bob and Mark did a fantastic job. Bob from the standpoint of providing a price that could not be beat and Mark for performing a flawless upgrade. As a bonus he configured ASMI port. Now I have full remote control access to the IBMi. From a powered off status I can start machine without manual intervention at the CO-LO and see status of machine before IPL. 

Total time for upgrade excluding software downloads and third party software upgrades was around 7 hours. One small bump with credit card processing software, they missed sending be the bulletin to change JAVA path. IBM changed the JAVA path to QOPNSYS which is where it should have been in the first place.

For under $3k Source Data Products cannot be beat. Now I have to quickly get up to speed with RDi Development Tools and V7R1 enhancements. Can't wait to utilize RUNSQL in CLLE, SDA and RLU in RDi.

SQL Update a Multi-Table View using INSTEAD OF Triggers

2013-02-21T08:35:00.000-05:00

Yesterday I added another SQL trick to my bag ‘o tricks. I am currently working on fixing EDI invoicing process and I needed a way to delete records from four files based on the same key. I could have wrote RPGLE Free Embedded SQL that would have been about 60 lines of code but wheres the fun in that.

I popped open the Google window and searched on “iseries sql delete from multiple tables”. the second hit directed me to IBM and the fourth to Preform the Impossible: Update a Multi-table View using INSTEAD OF Triggers. What did we did before Google, oh yeah, read a manual!

Impossible, that’s for me. It took me a few minutes to understand and a peek at IBM documentation I had to create a view and then the trigger. Once created any time I delete from the view all associated records in the four files are deleted. Very cool.

After view and trigger created I can run this SQL interactive or embedded.

delete from yourlib.ediinvw
where ihedoc = 0000046475

My new view is EDIINVW and I am deleting by EDI document control number.

To accomplish this I created the view with the below code using SQL Explorer.

create view yourlib.ediinvw as
select ih.ihedoc, id.idedoc, ss.iaedoc, si.isedoc
from yourlib.vedinih ih
join yourlib.vedinid id on id.idedoc = ih.ihedoc
join yourlib.vediniss ss on ss.iaedoc = ih.ihedoc
join yourlib.vedinis si on si.isedoc = ih.ihedoc

The four fields in the Select statement is the key field for each of the four files.

I then attempted to create the delete Trigger. SQL Explorer did not like the syntax so I copied and pasted into the green screen iSeries SQL interactive command line. My guess is that SQL Explorer has an issue with INSTEAD OF DELETE ON.

create trigger yourlib.ediinvdel
Instead of delete on yourlib.ediinvw
referencing old as EdiInvwOld
for each row mode DB2SQL
begin atomic
delete from yourlib.vedinih
where ihedoc = EdiInvwOld.ihedoc;
delete from yourlib.vedinid
where idedoc = EdiInvwOld.idedoc;
delete from yourlib.vedinid
where iaedoc = EdiInvwOld.iaedoc;
delete from yourlib.vedinid
where isedoc = EdiInvwOld.isedoc;
end;

I learned another very cool SQL trick, Thanks IT JUNGLE and Four Hundred Guru. This will come in handy over the next few weeks as I fix the EDI processes.

Have your best day!

~Richard

In programming, as in everything else, to be in error is to be reborn. ~Alan J. Perlis

iSeries SQL Update multiple columns

2013-02-14T10:06:00.001-05:00

SQL is cool s**t..I love this stuff.... Below SQL uses file created from spreadsheet and updates the min and max fields in the item balance file from one field from uploaded spreadsheet.

Spreadsheet fields VENDOR, VMFG, CAPITEM, LOC, MINMAX.

update b50files.vinitmb

set (ifsmin,ifsmax) = (select minmax, minmax from rbryant.upminmax

where ifcomp = 1 and ifloc = loc and ifitem = capitem)

where exists (select * from rbryant.upminmax where ifcomp = 1 and ifloc = loc

and ifitem = capitem)

Updated 04/29/15

I have a task to update several fields in file with calculated amount based on data from a different file. Since I do not write SQL everyday I do not try to remember and use Google. My google search took me to my blog from 2013. Just what I needed and now expanding complexity for future reference.

I need to take the location item price from item price file and reduce by 50% and update cost fields in item balance file. The real trick in my mind is the "exists". I fumbled around and could not get it work until I added the where select exists.

vinitmb is item balance file and vinpmat is item price file.

ifavg, ifoth and iflst are cost fields being updated.

ifdiv and ifcls are division class fields.

iflcgd, iflcgt and ifuslc are last change fields in case I screw up and need to back out. Of couse all of this is tested with Select statements and then updated test files to make sure.

update r50files.vinitmb
set iflst = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifavg = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifoth = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat
where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
IFLCGD = 20150429,
IFLCGT = 080000,
IFUSLC = 'SQLRB'
where exists (select * from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item)
and ifdiv = 9999 and ifcls = 2

~Richard

One man's crappy software is another man's full time job. ~Jessica Gaston

Clean iSeries output queue by days to keep

2013-02-14T08:20:00.000-05:00

It’s has been a fast six months, new job has kept me hopping. One day I’m redesigning the EDI guidelines and RPGLE programming interfaces to the next day chasing Purchase order suggestions. Tape backup failing without notification to GL year end posting to rewriting code replacing OPNQRYF with embedded SQL. Point of Sale hiccups to AP check failure to working with eCommerce. Every single modification that the previous software vendor implemented has a problem one way or the other.

Basically I have not been happier than any other point in my career!

I ran across the need to automate purging old spool file records and could not find any code close enough to cut, paste, compile, run and free. In the past have used TAATOOL but is not currently an option.

My good buddy Rick Santiago has a purge out queue by date program that he wrote a while back. Short and sweet I quickly envision modifying for my needs.

Through iSeries navigator I created a table with four fields, out queue name, out queue library, number of days to keep spool file entries and user data.

I also added the following key constraints, C_OUTQUE, C_OUTLIB and C_USRDTA as unique.

Follow your nose and instructions in the comments of the CL and you’ll have a quick little program you can add to your nightly process.

Hopefully this help some other poor one man band quickly solve a minor annoyance programmatically.

CLNOUTQ is the driver and CLNOUTQ2 exists due to only being able to use one RCVF in a CL in a read loop and detect end of file.

CLNOUTQ

CLNOUTQ2

~Richard

Try to discover

The road to success
And you'll seek but never find,
But blaze your own path
And the road to success
Will trail right behind.
~Robert Brault, www.robertbrault.com