Saturday, August 5, 2023

Search text inside files - Windows 10 Indexing

 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


Tuesday, June 13, 2023

Upgrade IBM i Operating System to 7.4

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

Tuesday, April 18, 2023

IBM i IFS mapped drive - Access Denied

 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

Wednesday, November 30, 2022

STRDBGSVR not starting

 

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.






Wednesday, November 30, 2016

Extract emailed attachment and upload to IBMi


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 –


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