Wednesday, December 28, 2011

No Internet Access When Connected with SonicWALL Global VPN Client (GVC)

While setting up a Sonicwall TZ100 with GVC VPN for a client I ran into a little issue. I was able to get connected but not able to browse the Internet from my local machine once connected. I am by no way a Firewall expert but I have successfully set up several over the years. Since I do not work on Firewalls every day I forget a few things, but that's what they made Google for!

I quickly found the below article - No Internet Access When Connected with SonicWALL Global VPN Client (GVC).

I double checked all my settings and found where I had missed a couple. Made the adjustments and VPN connection works great.


Article Applies To: 
Affected SonicWALL Security Appliance Platforms:
Gen5: NSA E7500, NSA E6500, NSA E5500, NSA 5000, NSA 4500, NSA 3500, NSA 2400, NSA 240
Gen5 TZ Series: TZ 100, TZ 100 Wireless, TZ 200, TZ 200 W, TZ 210, TZ 210 Wireless,
Gen4: PRO series: PRO 5060, PRO 4100, PRO 4060,PRO 3060, PRO 2040, PRO 1260
Gen4: TZ series: TZ 190, TZ 190 W, TZ 180, TZ 180 W, TZ 170, TZ 170 W, TZ 170 SP, TZ 170 SP Wireless, TZ 150, TZ 150 W, TZ 150 Wireless (RevB)

Firmware/Software Version: All SonicOS Standard and Enhanced versions.
Services: GroupVPN




Problem Definition:
You are able to access the VPN network when connected through Global VPN Client; however, you cannot access the internet.

You can do Global VPN Client (GVC) connections to SonicWALL firewall using Split Tunnels (the simplest method and most popular).  This allows you to access your VPN resources while using your own local internet connection for all other traffic (like web surfing).  You can also choose a 'Tunnel All' (or 'Route All') configuration in which all of your internet traffic is first sent across your client VPN connection, and is then sent out from that firewall's internet connection. 

If you wish to do Split Tunnels connections with GVC to a SonicWALL GroupVPN policy, but some settings are wrong, your internet can be blocked.  Similarly, if using GVC in a Tunnel All configuration, the firewall needs certain settings for internet access to work (see the NAT Policy at the bottom).
Possible Causes and Resolution: 
- Under GroupVPN configuration (on the VPN - Settings screen), enabling the following options could cause GVC to drop internet traffic.
  • Default Gateway (Default LAN Gateway in Standard OS) - Allows the network administrator to specify the IP address of the default network router through which incoming IPSec packets for this VPN policy should be directed. Incoming packets are decoded by the SonicWALL and compared to static routes configured in the SonicWALL security appliance. Since packets can have any IP address destination, it is impossible to configure enough static routes to handle the traffic. For packets received via an IPSec tunnel, the SonicWALL looks up a route. If no route is found, the security appliance checks for a Default Gateway. If a Default Gateway is detected, the packet is routed through the gateway. Otherwise, the packet is dropped.
  • Allow Connections to - This Gateway Only - Allows a single connection to be enabled at a time. Traffic that matches the destination networks as specified in the policy of the gateway is sent through the VPN tunnel. If this option is selected along with Set Default Route as this Gateway, the Internet traffic is also sent through the VPN tunnel. If this option is selected without selecting Set Default Route as this Gateway, the Internet traffic is blocked.
  • Set Default Route as this Gateway - If checked, this changes the Global VPN Client’s behavior to be a tunnel all configuration. If unchecked, the Global VPN Client must drop all non-matching traffic if Allow traffic to - This Gateway Only or All Secured Gateways is selected.  If checked along with Allow traffic to - This Gateway Only or All Secured Gateways, Internet traffic is sent through the VPN tunnel.
 
Note: If Set Default Route as this Gateway on the Client tab of the GroupVPN policy is unchecked and “Split Tunnels” is NOT selected, then Internet traffic is blocked. This option enables all remote VPN connections to access the Internet through this VPN tunnel. You can only configure one VPN policy to use this setting.
To confgure GroupVPN for Split Tunnels, follow these steps (for both SonicOS Standard and Enhanced):

Click the Edit icon for the WAN GroupVPN Policy. The VPN Policy window is displayed.
  1. Click the Advanced tab. Set Default Gateway to 0.0.0.0
  2. Click the Client tab. Set Allow Connections to - Split Tunnels.
  3. Uncheck Set Default Route as this Gateway.
  4. Click OK.
 
If the above settings do not provide a resolution, and if you are running SonicOS Enhanced, go to the Users - Local Users screen.  You will see that there is a VPN Access column.  If you mouse over each users’ VPN Access, the assigned and inherited network objects are displayed (in SonicOS Enhanced 3.x or greater).  Make sure for any split tunnel users, you don’t have the following objects configured:
- VPN DHCP Clients
- WAN RemoteAccess Networks
- any other network object which is unconfigured and thus has a value of 0.0.0.0

These kinds of objects can transform a Split Tunnel GroupVPN into a Tunnel All GroupVPN for any users who are assigned them, or who inherit them.  The more correct objects for VPN Accesspermissions are objects like ‘LAN Subnets’ or ‘Firewalled Subnets.’
 

If a remote user is still blocked from internet access when connected with GVC, you can check the following on the PC running Global VPN Client:
- Open a Command Prompt
- Type the command:  route print. 
- The route print should show only one route with a destination of 0.0.0.0 + 0.0.0.0, with the default gateway configured on the client PC. If it shows a second route with the GVC virtual adapter’s IP as the gateway, then you    have inherited a Route All Policy (possibly by accident).

To confgure GroupVPN for Tunnel All, follow these steps

Note: Only SonicWALL appliances running SonicOS Enhanced can route all internet traffic from the Global VPN Client through the VPN tunnel without help.  Appliances running SonicOS Standard and Firmware 6.x require a second internet gateway device on the SonicWALL LAN to accept the internet traffic.

SonicOS Standard:

Go to the VPN > Settings page. Click the Edit icon for the GroupVPN entry. The VPN Policy window is displayed.
  1. Click the Advanced tab. Set Default LAN Gateway to the IP address of a LAN based router / second Firewall. This second device must be capable of sending traffic to the internet without the SonicOS Standard firewall’s help.  Its IP address will be in the same subnet as the SonicWALL’s LAN IP address.
  2. Click the Client tab. Set Allow Connections to - This Gateway Only or All Secured Gateways.
  3. Check Set Default Route as this Gateway.
  4. Click OK.

SonicOS Enhanced:

The VPN > Settings page provides the SonicWALL features for configuring your VPN policies. You configure site-to-site VPN policies and GroupVPN policies from this page. Click the Edit icon for the GroupVPN entry. The VPN Policy window is displayed.
  1. Click the Advanced tab. Set Default Gateway to 0.0.0.0.
  2. Click the Client tab. Set Allow Connections to - This Gateway Only or All Secured Gateways
  3. Check Set Default Route as this Gateway.
  4. Click OK.


  5. Go to the Network - NAT Policies screen.  You must add a NAT policy which translates the traffic coming from the remote GVC user, as it goes through the WAN of the firewall towards the internet.  This is needed with or without the DHCP over VPN on the WAN GroupVPN Policy.  This NAT policy will not affect any traffic except traffic heading towards the internet from route all VPNs.  The NAT Policy should look like this:
    • Original Source:  Any
    • Translated Source: WAN Primary IP (or X1 IP)
    • Original Destination: Any
    • Translated Destination: Original
    • Original Service: Any
    • Translated Service: Original
    • Inbound Interface: X1 (or WAN)
    • Outbound Interface: X1 (or WAN)
    • Set Enable NAT Policy
    • Do NOT set Create a reflexive policy
    • Click OK.
KBID3523
Date Modified3/15/2010
Date Created10/16/2007


~Richard

SQL update not needed to resolve issue...

Sometimes I really feel like a putz. One of my clients asked me to change a program so that only active records from the Rolox file are displayed during searches. My initial thought is a two second change to either program to use logical file selecting on 'A' status records or a couple of lines of code in the RPG. I was partially right!

The Rolox file was designed by me in 1995 at the request of the owner of the company. He did not want to use Outlook contacts and wanted it in green screen. He also needs three fields to identify who would get a Christmas card, party invitation or wreath sent to an address as well as the standard contact information fields. The client wanted a one stop process for looking up phone numbers by name. 

Simple enough; I created the file with fields as needed and a maintenance program to add, update or change records in the file. There are three different types of records in the Rolox file, entries of people that are neither customers or vendors, customers from VAI VARCUST file and vendors from VAI VAPVEND file.  This process is created on the iSeries in RPG III and worked as required for six years.

I opened the Rolox file and through my due diligence I realized something is not right. I ran some SQL over the files to determine what the current status of the Rolox file and found many discrepancies.

select * from r37files/vapvend where not exists 
 (select * from r37ceudta/rolox where acdel = rxstus and acvend = rxid and 2 = rxecd);

select * from r37files/varcust where not exists 
 (select * from r37ceudta/rolox where rmdel = rxstus and rmcust = rxid and 1 = rxecd); 

There were thousands of records that did not exist in the Rolox file. This should not be the case since both the VAI customer maintenance and vendor maintenance programs were modified to add, change, delete from the Rolox file or at least so I remembered.

I then started looking at the VAI maintenance programs and found that the programs were not in the VAI modified library. This was a little disturbing. Before going any further I call the client and asked him to show me exactly where he was seeing the problem. First I realized that there are multiple menu options for the Rolox, maintain/edit and search. The client is using the search option and has not used the maintain/edit option in years.

   
At this point it all came back to me, back in 2000 I upgraded the client VAI S2K from version 3.2 to 3.7. During the planning stage I took advantage of the upgrade to get rid of the Rolox file and use the VAI customer and vendor files exclusively. The only additional fields are the party, card and wreath fields. Version 3.7 has a new feature called user defined fields. User defined fields can be created for the following files:
                                                           
     1. Setup                                                                 
     2. Item                                                                  
     3. Item Balance                                                          
     4. Vendor                                                                
     5. Chart of Accounts                                                     
     6. Customer                                                              
     7. Contacts                                                              
     8. Ship-To                                                               
     9. Prospect                                                              
    10. Opportunity                                                           

I had completely forgotten that I sold the client on allowing me to create a new inquiry program that would eliminate the Rolox file and only use the customer and vendor master files.

So the fix is a two second fix; I changed the following logical files to select records equal to status active only. They were originally coded as COMP(NE 'D')

-----------------------------------------------------
 VARCST90 - AR CUSTOMER MASTER LOGICAL BY NAME       
-----------------------------------------------------
         R ARCUSTR                   PFILE(VARCUST)  
         K RMNAME                                    
         S RMDEL                     COMP(EQ 'A')    

--------------------------------------------------
VAPVND90 - AP VENDOR MASTER LOGICAL BY NAME       
--------------------------------------------------
        R APVENDR                   PFILE(VAPVEND)
        K ACNAME                                  
        S ACACT                     COMP(EQ 'A')  

Recompiled the logical files and program.

I have done so many installations and modifications over the past ten years for many different clients and employers, I guess this one just slipped by me.

The client is happy and I am not charging him for a two second change that took me all day. While I was doing this I also installed and configured SQL Explorer on my PC and practiced SQL UPDATE, INSERT INTO, WHERE EXISTS, WHERE NOT EXISTS and SELECT. So not a total loss.

I have removed the old menu option and Rolox file from the system as I should have done years ago. 

It is a good feeling to know what I created ten years ago was the right thing to do and will stand the test of time.

~Richard



 

Tuesday, December 27, 2011

Good morning all...

I took a few days off for Christmas and now ready to crank up the job search again. My good programmer friend Rick Santiago, wife Lori and dogs Max and Annie came up to visit for the last 4 days. We really enjoyed having them here and had a great time.

I have some SQL to create today that will sync up two master files to one. This task will utilize UPDATE, INSERT INTO, WHERE EXISTS.

Have a great day,

~Richard

Friday, December 23, 2011

Dreaded "Automatic installation not complete" message...

While upgrading to i5/OS V6R1 from V5R4 I received a message I have not seen before, "Automatic installation not complete". I received this message after loading all the upgrade media and the IPL had completed. I proceeded to look for why the installation did not complete and found job log message 410196/QLPINSTALL/QLPINSTALL.
Message: Error while processing file MRMXH20010 in QUSRTMP.
Cause: Some objects may have been damaged. Save or Restore results cannot be predicted.
Tech Description: Error summary code E410. Device dependent error code.
CPPE468
MCH3601
CPD376B
Failed installation 5761XH2, 5761XW1,  *BASE


My belief is that the DVD was damaged. The client does not use the product but I would still like to see it installed. I plan on downloading and trying the install at a later date.

~Richard


 

Monday, December 19, 2011

Another successful upgrade to V6R1.....

Just got back home after another successful IBMi I5/OS V6R1 upgrade. I drove down to Ft. Lauderdale Thursday morning and stayed with my good friend and excellent programmer Rick Santiago. Him and his wife provided a place to sleep and great hospitality.

I spent 12 hours Friday, 12 hours Saturday and two on Sunday to complete the upgrade. I had an issue with installing one licensed program and failed to accomplish the extra credit of switching the console from Twinax to console on the LAN. I will post more details in the coming days.

The scope of work also included setting up a Sonicwall TZ100 firewall with VPN access.

Over all the upgrade is a success, and the company is functioning as normal this morning. The customer is very satisfied and has more work for me on the VAI System 2000 software setting up the General Ledger report writer and creating an Income Statement and P&L.

Have a great week!

~Richard

Tuesday, December 13, 2011

iSeries sessions not opening and EOD did not finish...

Sat down this morning and quickly noticed I am be requested to join a hangout named Help created by one of my clients.

Jason stated he cannot access the iSeries 5250 emulation screens. The sessions start up and are blank but show connected. Jason also stated that he cannot access the Operations Console.

I fired up a VPN connection and attempted to start a session with same result. The Operation Console started and gave me control panel access but no console session. I am a little confused why the console session would not start. I know that the problem is the QINTER subsystem is not started but did not realize that the console session would also run under QINTER. I have to look into that a little further.

With the Operation Control Panel up I could force an IPL but want to avoid if possible. I start up iSeries Navigator and expand Work Management and click on Active Jobs. I see the End of Day job with a message waiting. The CL attempts to shut down the subsystem and there is no monitor message code if the subsystem is already ended. The job halted and waiting for answer to message. Easy permanent fix to the program, never a problem before.

The problem was created when Jason inadvertently selected the wrong job scheduled entry to submit to batch. After he submitted the job he canceled it but the job had already progressed to the point of shutting down QINTER. Without realizing what was happening he left for the day.

I answered the message with I to ignore and let the End of Day complete normally. Problem resolved.

System I Navigator saves the day! With a little help from me. ;)

A simpler way would have been to just fire up a session in QCTL subsystem. Unfortunately we did not add a work station entry after the migration. I am fixing that now. This brings me back to why did the console session not start, it did not occur to me to add workstation entry since we now have Remote Console capabilities.



~Richard




Monday, December 12, 2011

Damage found on file QAYPSYSTEM in Library QMGTC

I had this same problem with my last migration but this time I won't let it slow me down. Always check full system save job log to make sure the save finished with no errors otherwise it will give you trouble during the upgrade.

Message ID CPF3285
Message - Damage found on file QAYPSYSTEM in library QMGTC.

I don't know what causes but problem but glad there is a fix so I can move on.

Management Central - File QMGTC/QAYPSYSTEM Damaged

 Incident Summary
Problem Summary: 
This document describes how to recover from a damaged QAYPSYSTEM file in library QMGTC.
Here are some of the error messages found regarding this issue:
CPF3285 -  Damage found on file QAYPSYSTEM in library QMGTC, was received during a system weekly and daily backups.
CPF8111 -   &8 damage on member &9 file &4. This message was found after a power failure .

Resolution: 
The QAYPSGRPCT, QAYPSYSGRP, and QAYPSYSTEM files in the QMGTC library need to be re-created or restored from backup. If these files are re-created, the system group constraints, the endpoint system list, and the group system list will be deleted. Therefore, whenever it is possible, restore the files from backup.
Note: Due to file constraints, the three files need to be all re-created, or all restored from backup.
Do the following to delete and re-create the files:
1.On the operating system command line, type the following:
ENDTCPSVR *MGTC , and press the enter key.
2.Delete the file QAYPSGRPCT in library QMGTC as follows:
On the operating system command line, type the following:
DLTF FILE(QMGTC/QAYPSGRPCT) RMVCST(*REMOVE) , and press the enter key.
3.Delete the file QAYPSYSGRP in library QMGTC as follows:
On the operating system command line, type the following:
DLTF FILE(QMGTC/QAYPSYSGRP) RMVCST(*REMOVE) , and press the enter key.
4.Delete the file QAYPSYSTEM in library QMGTC as follows:
On the operating system command line, type the following:
DLTF FILE(QMGTC/QAYPSYSTEM) RMVCST(*REMOVE) , and press the enter key.
5.On the operating system command line, type the following:
For V5R3:
CALL QSYS/QYPSSETUP PARM(V5R3M0)
For V5R4:
CALL QSYS/QYPSSETUP PARM(V5R4M0)
For V6R1:
CALL QSYS/QYPSSETUP PARM(V6R1M0)
Press the enter key.
For V7R1:
CALL QSYS/QYPSSETUP PARM(V7R1M0)
Press the enter key.
6.On the operating system command line, type the following:
STRTCPSVR *MGTC , and press the enter key.
Note: This process does not prevent the file(s) from getting damage again. If the problem with the damage object recurs, you should contact IBM Support.

References: 
None.

System i Support 


Monday, December 5, 2011

Copy source member from one machine to another...

In preparation for an upgrade from I5 V5R4 to V6R1 I needed to remotely apply PTF's and then apply permanently. The system I am currently working on only has Twinax Console which prevents me from running a Full System Save remotely. Once I finish the upgrade I will change the console to be LAN driven and resolve the issue. For now I have the need to do a couple of Full Saves and be able to accomplish this via remote.

I found an article a year or so back that provided example CL code on how to perform a full system save via remote or on a schedule.

Due to only having a Logmein connection to the Iseries I'm upgrading, I rather finish fine tuning the code using WDSC 6.0. I have a VPN connection to another client I recently upgraded so I developed the code on their machine and then needed to transfer to the machine I'm working on.

Here are the steps I used to transfer the code:

Modified source code to work for my scenario.

Create save file containing the source code.

        CRTSAVF FILE(QGPL/FULLSAV)

Save object source file to save file.
     
        SAVOBJ OBJ(QCLSRC) LIB(RBTEST) DEV(*SAVF) OBJTYPE(*FILE) 
          SAVF(QGPL/FULLSAV) TGTRLS(V5R4M0) FILEMBR((QCLSRC (FULLSAV))) 

Copy save file to IFS.

      CPYTOSTMF FROMMBR('/qsys.lib/qgpl.lib/fullsav.file') 
           TOSTMF(fullsav.savf) STMFOPT(*REPLACE)

Open iSeries Navigator and drag the save file to my desktop.

Using LogmeIn, I access a remote PC at the client site and transfer the save file to the PC with LogmeIn file manager.

FTP the save file to client iSeries using Windows command line FTP.

        Windows start / run type CMD enter.
         FTP XXX.XXX.XXX.XXX
         User name
         Password
         BIN
         CD QGPL
         PUT FULLSAV.SAVF

I like to play it safe so I will create a temporary hold library to restore the QCLSRC file and member.

      CRTLIB LIB(HOLD)

Restore object.

      RSTOBJ OBJ(QCLSRC) SAVLIB(RBTEST) DEV(*SAVF) SAVF(QGPL/FULLSAV)         FILEMBR((QCLSRC (FULLSAV))) MBROPT(*ALL) ALWOBJDIF(*ALL) RSTLIB(HOLD)

I then copy the source member to my testing library to complete the finishing touches and compile.

More on the code in the next blog.

~Richard




     

     

Monday, November 28, 2011

Official Google Blog: The evolution of search in six minutes

Official Google Blog: The evolution of search in six minutes

New challenges for new week...

Hope everyone had a Happy and Safe Thanksgiving.
I have to back burner my DB2 Query education and start planning another V5R4 to V6R1 upgrade. This will be an in place upgrade on a Power 5 model 520. After I complete this one I have one more client that needs to upgrade and I hope to get that one in December as well.
Thankfully the timing of the IBM withdrawal coinsides with my job status, enabling me to pay the bills while I search for a full time challenge.
If anyone out there needs to upgrade I am available and have a solid track record of successful upgrades.
Have a great week!

~Richard




Thursday, November 24, 2011

Happy Thanksgiving!

Have a wonderful and safe Thanksgiving!

~Richard

Tuesday, November 22, 2011

Logmein file transfer problem....

I periodically update item vendor costs for one of my clients in the aircraft engine overhaul and parts business. They use VAI System 2000 ERP software running on a iSeries 9405-520 V5R4.

This is normally a simple to do and the vendors always throw a curve every once in while to keep me from fully automating the process. The vendors only provide the prices via Excel spreadsheet format.

My process involves fixing up the data in the spreadsheet such as changing description to upper case, changing dollar amounts to two decimal places, calculating the actual cost based on discounts and changing numeric item numbers to text. I could do all of this on the iSeries but since I have data in Excel and the record counts are always under 50k, I just use Excel functions.

Once the spreadsheet is prepared for upload I use Logmein to access the clients iSeries. Logmein has a nice file transfer java applet that usually works very well. I transferred the file as usual and then I use iSeries Access to create the database file and transfer the data. When I got the scan step an error occurred "Incorrect function". I know of this error and it means that the format of the incoming file is not an Excel Workbook (BIFF8).

Ok, maybe I made a mistake (who me?) when I saved the spreadsheet, I re-saved and transferred. Same problem. I then started looking at the transfer process and found that Logmein was transferring the file with zero records. I have no idea what changed with Logmein and this is annoying.

As a work around I sent the spreadsheet to the client this morning via Email and uploaded with no issue. Hopefully Logmein will fix the problem before the next price update comes along.

It's always challenging, that's what keeps it exciting!

~Richard



Monday, November 21, 2011

Good Morning!

Good Morning!

After a price file update for one client I'm be back to figuring out DB2 Web Query. It is drastically different than Query/400 but so much more to offer.

Last week I managed to build synonyms and create a report of sales history for one day's sales of jeans. Then built pie chart showing percentage of colors shipped. I am attempting to build some trending analysis.

~Richard 


Friday, November 18, 2011

Finely, the System i Navigator has speed, no more green screen.

For many years I tried to use the System i Navigator for my daily operations task and it never was fast enough to enable me to switch from green screen. Since installing the new Power 7 all of that has changed. The interface fly's even over a remote VPN connection. Here is a good presentation on potential uses.
Great job IBM!!!


Wednesday, November 16, 2011

I love the remote Operations Console!!






























Remote PTF apply is just too easy.

~Richard

Error during nightly save operation


Soon after I installed and migrated to the new iSeries 8202-E4B I was informed that and error message from the nightly backup is being received by the system administrator. After diving into the job log I found the following:

40   11/16/11  00:12:46.435826  QSRGENSE     QSYS        *STMT    QSRGENSE    QSYS
From module . . . . . . . . :   QSROBJEC
From procedure  . . . . . . :   qsrSendObjectMsg__FP6qsrMsg
Statement . . . . . . . . . :   15
To module . . . . . . . . . :   QSROBJEC
To procedure  . . . . . . . :   qsrSendObjectMsg__FP6qsrMsg
Statement . . . . . . . . . :   15
Message . . . . :   Object in use.  Object is
/home/QIBMHELP/.eclipse/org.eclipse.platform_3.2.2/configuration/org.eclipse
.osgi/.manager/.tmp2354697704417634823.instance.
Cause . . . . . :   An operation attempted to use object

There are two of these objects that can not be saved and I am not really sure what they are and I had not run into this before. QIBMHELP is IBM Help Server Support interface for the Infocenter. It also provides a framework (using Eclipse) that allows applications to write to it.

I don’t know why it took me so long but after an hour of searching and learning all I could about QIBMHELP I found this IBM Tech Note.

Simple enough, if IBM suggests to omit it during the IFS save then so be it.

~Richard

Saturday, November 12, 2011

iSeries LAN Operations Console

For several years I have wanted to try using the Operations Console remotely. The problem is the companies I worked for already had Twinax connectivity and I could not justify the cost of adding the additional feature.

When ordering a new machine and you don't have any Twinax devices to support, it's actually cheaper to order Ops Console LAN feature. I am a little uneasy about not having a direct console connection onsite but if the network goes down there would be bigger problems than accessing iSeries.

Setup was easy following the instruction right out of the IBM manual. System i Network  also has a good article to read up on.  

It is just awesome to be able to control the entire system remotely. Even if I shut down the TCP servers I can still access the console to restart.


Thursday, November 10, 2011

One decimal place can make all the difference....



Over the last couple of days I have been working on installing an interface between Optimum Solutions Payroll (OSI) and VAI System 2000 (VAI) to automatically generate and post payroll General Ledger entries.

The process involved downloading what OSI calls an Opticom which contains the programs and files needed to post payroll General Ledger journal entries to VAI. Next I created a library GLI.VAI and through the OSI Opticom process I posted the downloaded Opticom to GLI.VAI.

Next, I had to update three OSI data area’s that identifies the GLI.VAI program library, VAI files library, and the new G/L interface menu. Step five from the instructions require that I move cross reference file to the OSI files library. As I typed the command I realized something is afoot. The library I created in step one, GLI.VAI is not the object library specified in the MOVOBJ command as documented. No biggie, I just changed it to what it should be and notified OSI of the inaccurate instructions.

I then added the appropriate records to the cross reference file. To make things a little easier I created a Query of the OSI GLMASTL1 file which contained all the defined G/L accounts in OSI and output to a file. I transferred the file to my PC and opened in Excel. I then added the corresponding VAI G/L account for each OSI G/L account. I also added an addition field to hold the company number. I named the columns exactly the same name of the fields in the OSI cross reference file. I then uploaded the completed spreadsheet back to the iSeries to my personal library. I then used CPYF command with *MAP to copy the records to the live cross reference file.

To test the process all I needed is the VAI system in test mode which requires I change the prefix of two data libraries in the library list from “R” to “T”. The programs OSI provided allows me to recreate the records from the last payroll run and populate VMOPOST file, VAI daily posting file. The process generates an error report of cross references errors and I had none.

The actual posting of the G/L journal entries are done during the VAI end of day process. To fully test, I triple checked my library list settings to make sure I am working with test files and, ran the end of day job. After a few minutes the job halted and display a program message, decimal data error.

I tracked the problem down to a numeric field in VMOPOST that was not initized when the records were added. This problem will occur when fields are added to end of a file and the program that uses the file has not been recompiled. I decided to just recompile the program that adds the records to the file. The compile failed, external field names exceed six positions. At this point I realize that I could convert the program to ILE but decide to consult with OSI helpdesk.

In our discussion we talked about the several different versions of VAI; R37.2, R37.4 and R37.6. There is a different Opticom for R37.6 and the one I was instructed to download only works with R37.2. No problem, 30 minutes later I had downloaded and installed the new version and reset my test data and the end of day finished with no error.

So another lesson learned the hard way, always give the complete version number of software you are working with. One decimal place made all the difference.

~Richard

Sunday, November 6, 2011

IBM SE's are like the Maytag repair man...





I received a call from one of my clients in South Florida reporting that the iSeries has a yellow light on the panel and could I look into it. 


A simple to do, I accessed a PC on their LAN via Logmein and started a 5250 session. Entered WRKPRB on command line and discovered indeed there is an error. SRC 2746B940 is displayed and upon further analysis I discover that the Twinax controller card is reporting failure. Evidently the issue first happened two months ago and was not noticed. This is one of those operation that the AS/400 iSeries is tucked under a table and only visited to take tape out and put in the next tape for daily save's. The Twinax connected console is not ever powered up and rarely used. 


I am not sure why this had not been a bigger problem since two printers are connected to the system via Twinax to IO box.
The machine is under hardware and software maintenance so a quick call to IBM to generate a PMR. Five minutes later I'm talking with a technician who verifies that the controller card is failing and he would dispatch a replacement card and systems engineer to install.


Next day, SE show's up after card delivered via Fed-Ex. After an hour the IBM SE is on the way out the door and the system is back to normal. 


I rarely get to see the SE's since I now work mostly remote and the iSeries rarely needs repair. I do have to look into why the system did not notify IBM on it's own. I am suspicious of some DSL / Firewall changes that may have broke the connection and plan on looking into the issue shortly.


~Richard

Saturday, November 5, 2011

How fast a week flies by....new Power 7 installation.



The installation of new IBM Power 7 and migration from V5R4 to V6R1 went relatively smooth. The full system save from V5R4 failed with object in library QMTC being damaged and took 5 minutes to resolve and run the save again. Lost 40 minutes overcoming that issue. I know there was a damaged object but could not find it without a full system save. I tried several of the recommended ways to find the damaged object before with no success.

I drove to Coral Springs, Florida from Jacksonville, Florida Thursday, about 4 ½ hour drive and spent the night at mom’s.

Got into the office @ 8:30am and promptly got to work checking the packing lists for the hardware and software. The hardware consists of new 8202-E4B Power 7 with 8GB of memory and 140GB of disk and operating system software loaded System I V6R1. A small machine but a real workhorse.

The best thing about this contract is that I get work with my good friend Jason, the IT Manager. As soon as I was finished with packing list and a cup of coffee we proceeded to open the Power 7 box. I wish I would have took some pictures. IBM delivers the system on a customized pallet just a bit bigger than the box itself. They make un-boxing super simple, pop out the four plastic clips on the side and slip the box up. The side of the inner box then falls to the side and then you just side the Power 7 off the pallet. This system is on wheels and no more than 120lbs, so it is easy to move around. This is not the rack mounted version. Jason likes to have all the equipment off the floor so he had a prepared spot on the table next to the Power 5 and we easily picked the system up and placed it on the table.

The 2 power supplies come disconnected and held in place with a retaining clip. Removed the clip and seated the power supplies into place. Plugged in the Ethernet cable from the switch and the the power plugs for the system. The system starts coming to life. It took me a minute to figure out that the display panel is a little push in display at the top of the machine has hidden from view. This system came with feature 5553 Ethernet console no IOP. The Operations Console will be on the LAN not directly connected to the Power 7. This is my first time installing a system that did not have Twinax console.

Jason and I had already installed the latest iSeries Access with PTF’s on his PC and my ASUS EEE PC1000 Netbook. After about ten minutes the system is ready to start configuring the Operation Console on Jason’s PC. It is really neat how this process now works. The Power 7 uses the BOOTP process to allow discovery of the system over the LAN automatically. After answering a couple of prompts we had Console display and Control panel on the screen. Pretty cool. I then tried with the Netbook over the wireless, no problem, really cool and impressive!

By now it’s around noon and the users were just finishing up, we had requested that users be done with the system by 1:00pm. My how time flies when you’re having fun.
Our next step is to install a 2844 PCI IOP card and a 5702 PCI IOA card in the old Power 5. The reason for this is that the current tape drive on the Power 5 is not compatible with the new LTO 4 drive in the Power 7. By installing the two cards we can attach a LTO 2 tape drive to the Power 5. LTO drives are backward compatible two levels. We shut down the Power 5 and carried it to a cleared desk. Slide off the side cover to gain access to the PCI slots. I already determined during the planning phase that the 2844 is goes into C1 and 5702 into C2. Flip back the container tabs to pull out the blanks and slide in the cards. Piece of cake, slice of pie!

The IBM Power systems are incredible, the machine discovered the cards and auto configured the tape drive. With the Power 5 back in play we started the full system save. I have been planning this for a couple of weeks so the devices, data, problem programs removed or identified. This is where I got bit with damaged object I mentioned above. So the total save time was 1 ½ hours.

Then following the instructions; restored the user libraries and system information such as user profiles. I varied a little from the book here, before running Object Conversion Program I ran RSTAUT. This is just if something failed during the conversion (not likely), I would be able to access the system with the transferred user profiles.

The Object Conversion was estimated to take 18 hours on the old Power 5 and I was not sure how long it would take on the new box. It’s now 6:00PM and I planned to end the day at this point.
We started the Object Conversion and left the office.

We got to office around 8:00am Saturday and found that the Object Conversion completed successfully and only took three hours. I proceeded with updating the saved system information with UPDSYSINF command. Identified the new Ethernet hardware resouce and made the configutaion changes neccssary to start up the TCP servers. Checked out subsystems and devices.

Now all the licensed programs,  QGPL and QUSRSYS  for V6R1 need to be loaded from DVD. This will upgrade the current user software to the current V6R1 version.

After all has been installed we restarted (IPL) the system. Fifteen minutes later we had console screen up. Logged in and checked QSYSOPR message queue. No real issues, license keys, ECS configuration and LDAP directory failure. LDAP not needed so no show stoppers.

Well... sort of no show stoppers. Previously the systems came shipped with the latest cumulative PTF DVD. Evidently no longer. I realized mid-after noon that we needed the latest Cumulative PTF’s and Group PTF’s . No problem we’ll just download them, right? Wrong,sort of! Placed electronic order and determined that 14GB of data needed to be download and would take 12 hours. BoooHisss! This is why I had a Sunday planned as a fall back if problem occured, had hoped to avoid it.

Retrieved the IBM Entitled software keys and applied to the system. This cleared up a few messages. We then updated the Seagull software since none of their objects were convertible. Ran the Optimum Solutions payroll update. Everything is pretty clean, so at 4:00pm we called it quits for the day.    
Based on the time required to apply PTF’s we decided that we would meet at the office around noon. I would also try to apply the PTF’s remotely.

I got up early Sunday and connected to the system by using Logmein on the Netbook using my Samsung Galaxy 7 tablet as a Verizon hot spot. What a pain, in the end it seems that the Logmein overhead and java where not playing well on the Netbook. I could not get a connection going for long. Unfortunately Jason had not configured the VPN so I could only use Logmein. It is now 10:00am so I decided I would go into the office, I could work in the parking lot using Jason's wireless. So there I sat in empty parking lot in Coral Springs applying PTF’s and controlling the system. Just too cool!!

When Jason arrived the PTF apply was 75% complete. We proceeded to removed cards we installed in the Power 5, they were rentals. Boxed up cards and tape drive. Started another full system save after PTF apply completed with no problems.We managed to get out of the office by 5:00pm.

There is one other failure, even though I have a vendor check list, none of the vendors could get me a key before the system was powered up. Usually I install a system one weekend and do the migration the next weekend. Around 6:00pm Friday I looked up at the clock and realized I failed to call the vendors. Should have had it on my main check list not separate document. Live and Learn.

I did not plan on being on location Monday morning but felt that until the keys are applied and users working I was not done. This is the first time I have caused production down time. It really is not that big of a deal for this client but a personal goal of mine. The CFO was fine with my progress and we had the users into the system by 10:00am. I left for home around noon, with a pat on the back and a check in my pocket. Yes I actually get paid for all this fun!

~Richard