ADABAS SQL Gateway CONNX with File Maker Pro

, , , , , ,
1

I have been testing CONNX ODBC to ADABAS with Excel, MSAccess, Crystal Reports and now working with File Maker Pro.
I can import data from the ODBC (CDD) data source with no problem.

Has anyone worked with any scripts to set up SQL access?

I have gone through the C# examples and understand, just curious if anyone has used File Maker Pro?

TIA

Mike Waldron

2

I have been able to create scripts for multiple table access, list displays, etc.

The script runs 3 seconds in INFONAUT and 1:56 in FMP and I am using Indexed Fields for the WHERE clause.
Seems kind of extreme to me.

SELECT a.intr_intake_type, b.work_last_name, b.work_first_name, a.intr_intake_dt, a.intr_intake_time, intr_screen_appr_dt, intr_intake_status, a.INTR_IR_NAME

FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a
inner join ADABAS_10_12_8_33.dbo.mW_worker as b
on intr_curr_worker_id = work_worker_id
where a.intr_intake_type = ‘ANE’
limit 16

3

Performance between infonaut and FMP should be equal - if anything FMP should be faster.

Are you certain that FMP and Infonaut are pointing to the same CDD. And are you also certain that the exact same query is being used in both cases?

To be 100% certain you can enable ODBC Tracing to verify the FMP is actually passing through the SQL to the CONNX driver, instead of returning the full contents of both tables into FMP temp tables and attempting to join in its own engine.

This issue may be better suited to address via support, but with the steps above I believe you should be able to determine the cause of the performance discrepancy.

4

I did turn on tracing for ODBC, but no log was created. am still trying

5

i did a cut and paste on the script and used the same cdd, there is only 1

6

Are you use 32bit FMP or 64bit FMP? You will need to enable ODBC Tracing using the ODBC control panel of the matching bitness

7

Windows 8 does have the option of 32bit and 64bit, 7 does not, thanks

8

Hi Mike,

Are you using 32bit Windows 7, or 64bit windows 7?

Thanks

lm

9

Hi Mike,

I have installed a trial version of Filemaker Pro 15 - 15.0.3.305 - 64bit.

I am able to import adabas data from the SQL Gateway, including joins from multiple tables, without any issue so far.

However I do not know if you are using the standard import feature, or if you have scripted some custom code in FMP.

I think the next step would be for you to contact support - then we can get exact diagnostics and perhaps have a screen sharing session in order to see what is happening on your system.

Thanks

lm

10

I agree. I believe it’s 32bit and will install the 64bit. Tried a lot of things. Finally, even though it’s not the same topology, I hooked up to a Replicated ADABAS SQL Server of the same files, all windows, SELECT, JOIN (basically the same script as FMP/CONNX)and was able to read 500k records in about 3 seconds in FMP 15. Still doing more testing.
Thanks for the response.

11

I also need to get ODBC tracing to work.

12

File Maker Pro 64 bit, CONNX32.DLL I imagine that’s 32 bit.
I have set up a 32bit dictionary and 74bit dictionary and ODBC data sources. With infonaut, response time is the same which is good. FMP 15 64bit, still has issues.

13

Larry what is your sql gateway/adabas topology? mainframe, z/os?
64bit dictionary or 32bit?
thanks
Mike

14

Hi Mike,

I believe this issue would be better handled via support. However I do not seem to be able to motivate you to create a support incident so I will do by best here :smiley:

I am using 64bit FMP - along with the 64bit CONNX driver, on 64bit Windows. I have tested with Adabas on z/OS, and Adabas on windows with the same result.

For Adabas, it makes no difference whether you use the 64bit data dictionary manager or the 32bit version. There is no bitness component to the CDD itself. The reason why we still have a 32bit data dictionary manager is that it enables customer who have 32bit only 3rd party ODBC Drivers to import those data sources in out our CDD.

Since you are accessing Adabas you can always use the 64bit version of everything.

Regarding the name of the CONNX Driver, for historical reasons it is CONNX32 - this applies to both 32bit ODBC and 64bit ODBC. The proper driver bitness will be loaded based on the bitness of the application that is making the ODBC call.
The 32bit drivers are located in C:\windows\syswow64.
The 64bit drivers are located in c:\windows\system32 (yes I know that seems backwards, but that is due to Microsoft history).

Regarding ODBC Tracing, I had no problem using ODBC tracing with FMP - but please make sure you follow the steps below.

  1. Before you enable ODBC Tracing, ensure that FMP is not running.
  2. Enable ODBC Tracing - using the ODBC control panel of the proper bitness.
    Please do this by running the ODBC administrator directly, instead of using the icon in control panel.
    The 32bit odbc administrator is here: c:\windows\syswow64\odbcad32.exe
    The 64bit odbc administrator is here: c:\windows\system32\odbcad32.exe
  3. You cannot look at the SQL trace log file size to determine if tracing is working. When tracing first starts, the file size will still say 0 for a while. However if you use notepad to open the 0 size file, you will see the trace information in there.

This all assumes you are running on a 64bit version of windows.

When I enabled tracing using the steps above - I could see that FMP was passing the join to CONNX, as shown below.

FileMaker Pro cf14-c930 ENTER SQLExecDirectW
HSTMT 0x0000008AFDE568F0
WCHAR * 0x0000008AA89548D0 [ 172] “SELECT “EMPLOYEES”.“FIRST_NAME”, “EMPLOYEES_ADDRESS_LINE”.“ADDRESS_LINE”\ dFROM “EMPLOYEES”, “EMPLOYEES_ADDRESS_LINE”\ dWHERE “EMPLOYEES”.“ISN” = “EMPLOYEES_ADDRESS_LINE”.“ISN””
SDWORD 172

FileMaker Pro cf14-c930 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)

It looks like FMP also performs a select count(*) as well - this will add to the query time vs infonaut.

FileMaker Pro cf14-c930 ENTER SQLExecDirectW
HSTMT 0x0000008AA27E6FB0
WCHAR * 0x0000008AA94E02D0 [ 116] “SELECT COUNT (*) FROM “EMPLOYEES”, “EMPLOYEES_ADDRESS_LINE”\ dWHERE “EMPLOYEES”.“ISN” = “EMPLOYEES_ADDRESS_LINE”.“ISN””
SDWORD 116

Could you enable tracing and send me the SQL trace log please?

Thanks

lm

15

Sorry about the not filing the log, you are right. I have been waiting to get over my misteps before filing, that way my facts I have created are more reliable. I am still reading this and I really appreciate your responses and superb effort.

bbs and thanks again

16

p.s I have to be careful of disclosing any data, will get the trace soon, thanks

17

p.s I have to be careful of disclosing any data, will get the trace soon, thanks

18

With your help and the 64 bit driver setup I was able to get a trace. Thanks

here it is

there is no actual data from the files, so I can attach it.

thanks

Mike
SQL.LOG (41.7 KB)

19

So I guess the issue here is how do we prevent FMP15 from issuing the COUNT??? If I file a log, it will probably have to be with FMP.

MW

20

Hi Mike,

Thank you for providing the trace. Your identification of the cause of the performance problem was correct.

It looks like FMP just automatically (and incorrectly) assumes that it can just remove all the select columns from the query, replace them with count(*). I believe it does this because their GUI actually displays a progress completion bar, and in order for that to work they need to know the total number of records returned ahead of time.

Their technique will work in almost all cases, but not this one - and it is due to the use of of the “limit 16” clause.

This is the problematic query from the log:

SELECT COUNT (*) FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a inner join ADABAS_10_12_8_33.dbo.mW_worker as b\ don intr_curr_worker_id = work_worker_id \ dwhere a.intr_intake_type = ‘ANE’\ d limit 16

In the query above, the limit is placed on the number of rows returned by they count(*) query - which will always be 1, so the limit has no effect at all.

This results in two problem: 1) The returned count does not actually reflect the number of rows returned by the query without the count.
2) If the tables being joined are large, the count query will take some time to process.

Do you have any other criteria you could use for the query to limit the resultset size other than using the limit clause?

Thanks

lm