Troubleshooting DataGate® for SQL Server

---

Troubleshooting IBM i TCP/IP Connectivity

If you are unable to connect to the IBM i database from ASNA's DataGate® Database Manager, the following information may help identify possible incorrect setups on the IBM i.

It is assumed that TCP/IP configuration has been completed correctly in Windows. If you are unsure whether TCP/IP is installed or correctly configured in Windows, contact your network administrator for assistance.

TCP/IP names are a combination of host names (machine names) and domain names. A machine name for TCP/IP does not have to match the IBM i machine name; however, it will be a unique name - and it can be the same as the IBM i machine name.

Throughout this example, the machine name used is 'machine', and the IBM i name is actually 'S1037242'. The domain name used throughout this example is 'domain.com'. Together the machine name and the domain name make a 'complete' TCP/IP name - 'machine.domain.com'. At your site, you should use your own domain name and own machine name.

1. First check to see if you are able to ping the IBM i by name from Windows. For example, if the TCP/IP name of the IBM i is 'machine.domain.com', then the following command should be issued from your machine from a Command Prompt:
   
   ping machine.domain.com
   
   • If you do not get a 'reply' from the machine that was pinged, then TCP/IP may not be configured correctly, either on your machine or on the IBM i. If you receive a "Bad command or filename" error message when typing the ping command, your machine may not have TCP/IP installed. Please check with your network administrator to make sure your machine has TCP/IP capability.
   • If you do get a response from the IBM i when the ping command is issued, then it is still possible some settings on the IBM i may be incorrect. From a command line on the IBM i, issue the following command: cfgtcp
   
   
2. From the Configure TCP/IP menu screen that appears, select the Work with TCP/IP Interfaces (option 1).
3. The following menu screen will appear.
   
   You should see something similar to this screen. If you do not, then TCP/IP is probably not configured on the IBM i properly, or it may not be completely installed. Check with your IBM i administrator to make sure the IBM i has TCP/IP installed.
   
4. If there is an internet address other than 127.0.0.1 listed, select that address and enter an option 5 command to display the setting for that IP address.
   
   
   • The Interface status should indicate Active. If it does not, then the TCP/IP services, although installed, may not be started, or may have terminated. The TCP/IP services should be started.
   • Try re-connecting to the IBM i database after restarting the TCP/IP services. (To start the TCP/IP services you must have sufficient authority rights and issue the following command from a menu prompt: STRTCP). After you have done this, then issue another ping command to see if you get a reply.
   
   
5. If you reach this point and are still having problems accessing the IBM i via TCP/IP, then F12 back to the main configuration screen for TCP/IP (Configure TCP/IP) and enter option 21 (Configure Related Tables) from the main TCP/IP configuration menu.
   
   
   • On the Configure Related Tables menu screen that appears next, select option 1, Work with Service Table Entries. You will then see the Work with Service Table Entries menu screen. Scroll through the list of services to find the DataGateServer entry.
   • If there is no entry here, that is the problem. Make sure you perform the installation section for the IBM i as stated earlier in this chapter.
   • If the DataGateServer entry is found, then the default port will be 5042 (unless it was installed with a different port). It is strongly suggested to use Port 5042.
   
   Note:The protocol listed in the service table entry for the 'DataGateServer' service should be 'tcp'. It is case sensitive, so if the letters are uppercase, you will need to remove the service and re-add the service with lower case letters.

If you are unable to start the DataGate® Service, the following steps will assist in trouble shooting the possible causes.

When the DataGate® service job is submitted for execution, the job is submitted under the user profile of DG8SVCPRF. This user profile is created during the installation and the system value for the Printer option will be assigned to this user profile. If the DataGate® service job has trouble starting, a one page report outlining the possible cause of the problem will be generated for the user DG8SVCPRF and printed to the assigned printer. This report will not print unless the Output Queue associated with this printer automatically prints the generated reports. Otherwise, to view the list of reports that have not printed for this user, enter the following command:

WRKSPLF SELECT(DG8SVCPRF)

At this point you will need to contact ASNA for further assistance.

The most probable cause for the DataGate® service job not starting is that the current DataGate® service job is terminated with an ENDJOB command, so subsequent attempts to start the service will not be successful.

1. If the DataGate® service job is not currently running and it won't start by issuing the STRDG8SVR command, enter the following command:
   

    NETSTAT 

2. Select Option 3. Work with TCP/IP Connection Status.
3. Select F14 to Display Port Numbers In the Local Port column, locate the Port Number you assigned as the Port for the DataGate® Service (The default Port Number is 5042).
4. You will need to end this connection by selecting Option 4.
5. When this connection is ended successfully, you may now execute the STRDG8SVR command.

If you notice that a DataGate® Job is still running after a client connection is lost, this is due to a TCP/IP feature called "Keep-Alive". Refer to the following to change the system's default Keep-Alive value.

Keep-Alive packets are used to probe a connection that has been inactive for a long time. The server initiates a disconnect when the probes do not get through. This means that if the client connection is lost for a prescribed length of time, the server sends a disconnect message to its server job (usually the result of a system crash or power-down).

The default Keep-Alive setting for Windows and IBM i is two hours, meaning that a DataGate® thread or DataGate® job would remain active for 2 hours after connection was lost with the client. The 2-hour default may be an unsatisfactory period of time for you, as files would continue to remain open, etc. on an abnormal disconnection until the Keep-Alive time had elapsed.

Refer to the following to modify the server machines' respective Keep-Alive period.

IBM i

1. Request the Configure TCP/IP menu by executing the command: cfgtcp
2. Select menu item 3 - Change TCP/IP Attributes.
3. Set TCP/IP Keep-Alive to the number of minutes desired. (Note: 2 to 5 minutes should work well in most cases).

Windows

WARNING: Using Registry Editor incorrectly can cause serious, system-wide problems that may require you to reinstall Windows to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk. The following was derived from Article ID Q120642.

To Change these parameters, use the following procedure:

1. Run Registry Editor (REGEDT32.EXE or REGEDIT.EXE).
2. From the HKEY_LOCAL_MACHINE subtree, go to the following key:
   \SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
   
   
3. The Value is " KeepAliveTime". If present (usually not), double click on it to edit in milliseconds. Otherwise, continue with Step 4.
   
4. Select "Edit -> Add Value".
5. For Value name, enter KeepAliveTime.
6. From the Data Type drop down list, select REG_DWORD
7. Press OK
8. In the DWORD Editor dialog, enter the number of milliseconds. Select the Radix Decimal option.
9. Select OK.
10. Enter the Keep-Alive value in milliseconds; e.g. 5 minutes = 300000 msec.
    
    Valid Range: 1 - 0xFFFFFFFF
    Default: 7,200,000 (two hours)
    
    The parameter controls how often TCP attempts to verify that an idle connection is still intact by sending a Keep-Alive packet. If the remote system is still reachable and functioning, it will acknowledge the Keep-Alive transmission. Keep-Alive packets are not sent by default. This feature may be enabled on a connection by an application.
    
11. After typing in the value, use the "Data Type" checkbox to set the value type.
12. Select OK.
13. Exit the Registry Editor.
14. Reboot the system to make the change take effect.

Basically, to set up a subsystem, you need to have a routing entry with a compare value of "QCMDB" that runs the program QCMD in QSYS.

The subsystem that is used for the job queries must be set up with a proper routing entry in order for DataGate® service to start. This routing entry must run the program QCMD in Library QSYS and have the compare value of 'QCMDB' or '*ANY'. If the subsystem has one of these routing entries, it may be used for DataGate.

If DataGate® is shut down and there are users connected, their jobs will still be out there and you have to manually end them. However, after manually ending the users' jobs, DataGate® may not start for about 5 minutes. It will start the first DG8SVC job and then end it until the 5 minutes (or so) is up.

It is likely that OS/400 is not reclaiming the DataGate® TCP/IP port (5042) in a timely manner when the service and connection jobs are ended. When you "manually end" connections to DataGate, it may not have a chance to properly shut down TCP/IP resources. The system typically does not realize that those resources are no longer in use for some quantum.

The best way to avoid this is to let the connections and service end normally, though in abnormal situations this isn't always possible.

When you must use ENDJOB, etc., to end the connections and/or the service, you can sometimes use the WRKTCPSTS command to view the outstanding socket connections and end them. In such a case, if you find a socket in the "listening" state using the DataGate® port, you can end this connection (but only if you are absolutely sure the service isn't running) using WRKTCPSTS. Likewise, if you find sockets connected to the DataGate® port from external ports, you can end those (if you are sure that there are no jobs using the connection). The system will then reclaim the port, and a new instance of the DataGate® service can use it immediately.

If the user does not know the IP address of the IBM i to which they are trying to Name a database, do the following. Note however, that most users should never have to do this, and you may want to "call your Network Manager" instead of performing the steps below.

1. In Windows, open a Command Prompt window and enter:
   arp -a
   Note the IP addresses that appear. You will compare this list with the list generated in step 4. (Normally, there will be only one difference, and it will be the addition of the IBM i's IP address).
   
2. For Windows: At a Command Prompt, enter:
   
   IPCONFIG
   
   The IP address of Windows will display. Note this IP address, since you will use it in step 3.
   
   
3. On the IBM i on a command line enter:
   
   PING 'xxx.xxx.xxx.xxx'
   
   where xxx.xxx.xxx.xxx is the IP address of Windows noted in step 2.
   
   In the Configuration dialog, there's a drop down list box of adapters. Note that in most cases you will only have one Ethernet adapter, so the only challenge is to distinguish the Ethernet adapter from a dial-up adapter. This is usually easy, because dial-up adapters usually have a name like "NdisWan4".
   
   
   It is also wise to NOT attempt to ping a Windows computer that is also running 5250 emulation, etc., to the object IBM i. That's because that IBM i will already be in the arp sorted "list", so one would not be able to tell which is the target IBM i because the list would be the same in steps 1 and 4.
   
   
4. On Windows in the Command Prompt window, enter:
   
   arp -a
   
   The IP addresses are sorted. Compare the list with the list in step 1 and note the new IP address, this is the IBM i's IP address.