Saying Goodbye

Though it has been a rather adventurous 20 years consulting the IBM/Sterling Commerce Gentran Server line of products, it is time to say goodbye.  In short the writing is on the wall and as the old saying goes, "IBM, is where good products go to die.". So with that I have brushed up on my certifications and have moved onto a new integration platform and am closing this chapter of my life and this blog out. I will leave it up for at least as long as it is seems logical to do so as a reference, but baring a resurgence of interest in the product, this will be my final entry. I would like to thank everyone that has followed and supported this blog over the years, it was you guys that made the hours of typing worth while. Thank you for all your support.

So for those that are curious, I have moved over to the Mirth Connect arena servicing the Health Care industry. Will I start a new Mirth Connect blog? Only time will tell. -Stephen

Gentran Archives and Audit Log Purge Not Completing due to SQL Log Error

So recently I really had to jump through some hurdles to clean up a Gentran Server. Scenario: Archives and Audit Log Purge were running weekly and daily (respectively) but we not completing do to SQL database log being full errors:

Process Control - [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'GENTRANDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Error returned: 0x80040E14
IDispatch error

Now the official solution from Sterling Commerce in the past has been to adjust the number of days back until the process can complete a full run, then keep adjusting and running the process until you get back to the desired number of day. I've had to do this and it sucks and the problem isn't even Gentran, it's an SQL issue. Not to mention that the server I was on had over 300 million log entries going back to 2012 and they were already having disk space issues. So after a bit of research this is how I got the server back into shape over the course of a weekend.

1st I ran the following SQL script on the SQL Server so I could capture the size of the Gentran servers SQL log file.

 
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'GENTRANDatabase' 

2nd, I had to reclaim some disk space. So besides all the normal stuff like clearing tmp files, and old download stuff, I wanted to capture and use the 2GB that was being used by the existing Gentran database log file. To do that I found the following SQL script on  MSDN, which if ran correctly should quickly chop that file down to about 1MB.

 
USE GENTRANDatabase;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE GENTRANDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (GENTRANDatabase_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE GENTRANDatabase
SET RECOVERY FULL;
GO

3rd, We need to set it so the GENTRANDatabase log file can grow without limits for a while. In the SQL Server administrator application, Right click on the Gentran Database and select Properties.

When the Properties window comes up select the Files page, which should have both a database and a log entry. Select the Autogrowth option button for the Log entry.

When the Autogrowth screen comes up select the Unrestricted File Growth option and select OK, then OK again when you get back to the Gentran Database properties.

4th, At this point try running your archive or audit log purge. Note, use discretion, if it's a big one you may want to cut the days in half and just repeat the above process if you feel that disk space is becoming an issue due to the database log size growing to fast.

Final note, once you are happy with the results don't forget to reset the log file size back to either the size you found it. By default on Gentran we would normally set the log to 2GB for a medium volume system.

FTP - Solution to only delete files that have been successfully downloaded

I know ftp and mget have been a round for a long time; however, just doing an mget followed by an mdel has a few major flaws. 1) mget does not have a delete on successful download and 2) mdel does not check to see if the file was received. With some of the notoriously bad internet connections out there it is very possible that the mget failed and the mdel is not aware of this when it is executed.

So this solution actually took a while to come up with, as the true art of windows batch script development has almost all but been lost. To start with we need to script our ftp connection to connect to our remote host, change directories and then download all files. I call this script ftpdown.scp,

ftpdown.scp

 
onerror exit
USER myuser
PASS mypass
CONNECT 10.10.10.90
CD /u02/out
lcd c:\in
mget *.*
close

Next I create a main batch file I call ftpdownmain.bat. This is this main script a user will execute. The script will first execute the windows ftp command along with the above script and download all the files. Afterwards it queries a directory listing of just file names and puts these names into a file I call downlist.txt. Important Note: downlist.txt and ftpgetlist.scp are created and removed automatically so there is nothing for you to do with these.

ftpdownmain.bat

 
c:
cd gensrvnt\scripts
ftp -s:ftpdown.scp

dir /b c:\in\*.* > c:\gensrvnt\scripts\downlist.txt
call c:\gensrvnt\scripts\ftpscpcreate.bat
c:
cd gensrvnt\scripts
ftp -s:ftpgetlist.scp

del c:\gensrvnt\scripts\downlist.txt
del c:\gensrvnt\scripts\ftpgetlist.scp

exit

ftpscpcreate.bat - Use this to Move files

 
echo onerror exit > c:\gensrvnt\scripts\ftpgetlist.scp
echo USER myuser >> c:\gensrvnt\scripts\ftpgetlist.scp
echo PASS mypass >> c:\gensrvnt\scripts\ftpgetlist.scp
echo CONNECT 10.10.10.90 >> c:\gensrvnt\scripts\ftpgetlist.scp
echo CD /u02/out >> c:\gensrvnt\scripts\ftpgetlist.scp
for /f  %%i in (c:\gensrvnt\scripts\downlist.txt) do (
  echo rename %%i archive/%%i >> c:\gensrvnt\scripts\ftpgetlist.scp
)
echo close >> c:\gensrvnt\scripts\ftpgetlist.scp

ftpscpcreate.bat - Use this to Delete files

 
echo onerror exit > c:\gensrvnt\scripts\ftpgetlist.scp
echo USER myuser >> c:\gensrvnt\scripts\ftpgetlist.scp
echo PASS mypass >> c:\gensrvnt\scripts\ftpgetlist.scp
echo CONNECT 10.10.10.90 >> c:\gensrvnt\scripts\ftpgetlist.scp
echo CD /u02/out >> c:\gensrvnt\scripts\ftpgetlist.scp
for /f  %%i in (c:\gensrvnt\scripts\downlist.txt) do (
  echo rm %%i >> c:\gensrvnt\scripts\ftpgetlist.scp
)
echo close >> c:\gensrvnt\scripts\ftpgetlist.scp

Just a final note, you may have to play around with the commands depending on the server you are connecting to. Feel free to contact at support@ediassociates.com if you have a special requests.

Gentran:Server - SQL Volume Report

Here is a basic Gentran Server volume report we recently put together. Just a sample of the custom Gentran Server work we do here at EDI Associates. Feel free to contact at support@ediassociates.com if you have a special requests.

 select distinct b.PartnerName,   
 a.TransactionSetID,   
 count(a.TransactionSetID) myCount,   
 direction = CASE a.Direction   
   WHEN 0 THEN 'IN'  
   WHEN 1 THEN 'OUT'  
 end   
 from dbo.Document_tb a, dbo.Partner_tb b   
 where a.PartnerKEY = b.PartnerKEY   
 group by b.PartnerName, a.PartnerKEY, a.TransactionSetID, a.Direction   
 Order by myCount DESC  

Output

GENTRAN:Server send an attachment through the E-Mail gateway

Recently I was asked if there was a way to send a transaction as an attachment through the E-Mail gateway. Just a bit of history: E-Mail gateway by default will send transactions as the message text of an email.

The answer to our question is YES; however, it is one of those wonderfully undocumented features that is not a straight forward setup.

Follow these steps:
Open your GENTRAN:Server Mailbox Manager > Right click on the E-Mail Mailbox your trying to send data with. > Select Properties > then the Gateway tab > and then Configure.

Next Select Additional Mime Headers and add the following: "Content-Type: Application/X-EDI;"

Your all done, just keep hitting OK until your out of the mailbox properties.

Please Note that a couple readers of this Blog have pointed out that there is another Mime Header you can try if for some reason you do not get the desired result using the above:
"Content-Disposition: attachment;"

Happy Gentraning.

GENTRAN:Server Notifications on stalled events

Do you have an event that occasionally stalls. There are many reasons an event might stall: network errors, unexpected data values and poor code just to name a few. My goal here is not to give you or anyone a hard time about that one program you wrote like 5 years ago; however, I'm sure it would be fun. My goal is to show you how to setup a notification so that the next time it does get stalled your not stuck with 3 or 4 days in unsent transactions and a pissed off boss.

First take note of the Session and Command that is stalled:

Next, you will have to stop the event that runs the session. If the event is currently stalled you will have to stop and restart the "GENTRAN:Server Executive" service. This can either be done from Services under the Control Panel, or from the command prompt using the following commands.

NET STOP "GENTRAN:Server Executive"
NET START "GENTRAN:Server Executive"

Once you are able to suspend the event under the Events tab you will be able to edit the session under the Sessions tab. Set the Notification flag and them set the number of minutes the process should be allowed to run before it sends you the notification. If you are uncertain, run the event and see how long it normally takes to complete.

Now it's time to setup the notification. The Audit Number is 1-3-1591, you will need this to setup the notification and it normally looks like the following in the Audit Log.

Open Gentran Server for Windows Configuration and select the "Audit/Notification" tab. Click on the "Notifications" button, the Gentran Notifications windows will open. Create a new Notification.

Select an operator to receive the notification.

Select OK and your all done. Note if you do not have operators or notifications setup they will have to be setup first. Do to the number of steps that will have to be a topic for a future post.

XML Common Root Tag

Have you ever cursed your software vendor for their seemingly endless lack of foresight when it comes to their choice of XML Root Tags? Boy I have... And they always want to charge you big bucks when you desire a necessary correction. As a professional integrator I love reusable code libraries and if you have ever worked with multiple XML documents in a single project you probably have guessed why I say this. Here are a few personal reasons why I like them.

• Reusing code (and Root Tags) saves programming time, which reduces costs.
• Sharing code  (and Root Tags) can help prevent bugs, basically the more code a system contains (to process multiple Root Tags)  the more bugs it's likely to have. 
• Separating code into common libraries lets programmers specialize in their particular strengths. 

Ok, time for me to get off the high horse. Just because our latest ERP project had some poor design work done on their EDI gateway, doesn't mean I need to complain about it. Besides this is a site for posting solutions, so here is my latest. The following script does one thing and only one thing very well. It will take an XML document and give it a new Root Tag, both start and finish.

The script is written in vbScript and it will add a common Root Tag to every XML file it finds in the input directory. Command Line is simple and is easy to script.

Enjoy and feel free to comment.

Stephen

 option explicit  
 Private strRootDir  
 Private strLoadDir  
 Private strOutputDir  
 Private strArchiveDir  
 Private objFSO  
 Private myStr  
 call Main()  
 sub Main  
      if (LoadParams) then  
           set objFSO = CreateObject("Scripting.FileSystemObject")  
           if (not objFSO.FolderExists(strLoadDir)) then objFSO.CreateFolder(strLoadDir)  
           if (not objFSO.FolderExists(strArchiveDir)) then objFSO.CreateFolder(strArchiveDir)  
           call ProcessFiles  
      else  
           DisplayUsage  
      end if  
 end sub  
 private sub DisplayUsage  
  WScript.Echo "Usage: cscript.exe Add_Root.vbs <RootDir> <OutputDir>"  
  WScript.Echo "Example: cscript.exe Add_Root.vbs C:\Users\Stephen\test\Load C:\Users\Stephen\test\Archive"  
  WScript.Echo "Example: cscript.exe Add_Root.vbs C:\Users\Stephen\HDTestInvoices\input C:\Users\Stephen\HDTestInvoices\output"  
 End sub  
 private function LoadParams  
      dim blnResult  
      if (WScript.Arguments.Count = 2) then  
           strRootDir = WScript.Arguments(0)  
           strOutputDir = WScript.Arguments(1)  
           if (right(strRootDir, 1) <> "\") then strRootDir = strRootDir & "\"  
           if (right(strOutputDir, 1) <> "\") then strOutputDir = strOutputDir & "\"  
           strLoadDir = strRootDir  
           strArchiveDir = strOutputDir  
           blnResult = true  
      else  
           blnResult = false  
      end if  
      LoadParams = blnResult  
 end function  
 private sub ProcessFiles  
      dim objSource  
      dim strExt  
      for each objSource in objFSO.GetFolder(strLoadDir).Files  
           strExt = ucase(mid(objSource.Name, InStr(objSource.Name, ".") + 1))  
           if (strExt = "XML") then  
                call ProcessFile(objSource)  
                objSource.Move strArchiveDir  
           end if  
      next  
 end Sub  
 Private sub ProcessFile(objSource)  
      dim objFile  
      dim strLine  
      dim arrFields  
      Dim intIndex  
      Dim blnIsHeader  
      Dim strTranID, strToMail  
      Dim detTranID  
      Dim s1  
      Dim strWorking   
  strWorking = strLoadDir & objSource.Name  
      Set objFile = objFSO.OpenTextFile(strWorking,1)  
      while (not objFile.AtEndOfStream)  
           strLine = objFile.ReadLine  
 '**************************************************************************************************  
           If ((Len(strLine) > 1) and (strLine = "<?xml version=" & """1.0""" & " ?>")) Then  
             strToMail = strLine & "<EDIDOC>"  
             WScript.Echo strLine  
           else  
     strToMail = strToMail & strLine  
     WScript.Echo strLine   
           End If  
 '*****************************************************************************************************  
      Wend  
      strLine = "</EDIDOC>"  
      WScript.Echo strLine  
  strToMail = strToMail & strLine  
  objFile.Close  
      Set objFile = objFSO.OpenTextFile(strWorking,2)  
      objFile.Write(strToMail)  
      objFile.Close  
 end Sub