It would be there, but if it actually exists and gets logged it's somewhere in the bowels of the MSSQL JDBC Driver and not in Ignition, and I don't know where or what logger it would be. Since you're not seeing it, if it exists, it's not configured for a logging level that can be seen by default.
Well I set my best buddy Claude on the task and you might try these loggers:
Primary Loggers
| Logger Name | Level | Purpose |
|-----------------------------------------------------------|--------------|----------------------------------------|
| com.microsoft.sqlserver.jdbc.internals.SQLServerStatement | FINE / FINER | Batch insert parsing fallback messages |
| com.microsoft.sqlserver.jdbc.SQLServerBulkCopy | FINER | Bulk copy operations, row processing |
| com.microsoft.sqlserver.jdbc.Statement | FINER | External API entry/exit for statements |
Supporting Loggers
| Logger Name | Purpose |
|------------------------------------------------------------|--------------------------------------|
| com.microsoft.sqlserver.jdbc.internals.SQLServerConnection | Connection-level internal operations |
| com.microsoft.sqlserver.jdbc.Connection | Connection API entry/exit |
See if they exist in Ignition when searching for loggers, and if they do, set the level to TRACE.
This is the extent of the loggers available in the Log Configuration app filtered for anything with ‘jdbc’ in the name. Nothing particularly helpful is added to the log with all the mssql jdbc entries set to ‘trace’.
Also, I did try (as Phil suggested) to allow Ignition to create the logging table (sqlth_1_data) using datetime2 for the datetime field and real for the 64 bit floating point number and float for the 32 bit floating point number in the translator. I’m still not seeing any bulk insertions.
The MSSQL JDBC driver packaged with Ignition 8.3.2 is version 9.4 from December 2021. There’s been a lot of revisions since then to this package. Would it be possible to download the latest and try that instead?
You can always upgrade the JDBC drivers. If it doesn't work then a rollback is pretty easy.
OK, I think I’m making progress. I installed the latest MSSQL JDBC driver (13.2.1.jre11.jar), added Extra Connection Properties trustServerCertificate=true;useBulkCopyForBatchInsert=true and now I’m seeing this on the SQL Profiler:
With the latest JDBC driver, two special settings listed above, and changing the type of the datetime column pretty much solves this issue. How much it really helps it’s hard to say as when you’re writing to a plain old table (with just a clustered key & no triggers) and wrap all the inserts around begin & end transaction statements, the SQL host load difference between the two isn’t maybe all that much. Regardless, you’re always better off working with sets of records rather than one-at-a-time. So at least to me it just feels better this way.
I did try bulk inserting to a view after getting this to work and as Phil predicted this didn’t work. But I still need to store trend data in a format more compatible with existing downstream processes. Modifying all downstream processes to consume Ignition historian data is a much heavier lift than than presenting Ignition with what isn’t but looks like Ignition formatted data.
The downstream process modification lift is the major factor. Another issue is the 100 bytes/tag change for sqlth_1_data. If you’re willing to give up the datetime, string, and quality fields (just inserting NULL when quality isn’t good) and store 32 bit floats instead of 64 (what PLC uses 64 bit floats?), and store integers in the float column, you can get this down to 24 bytes/tag change. With disk space less than $50/terabyte who gives a shit but you still have to back it all up, ship the backups offsite over slow links and recover in a timely manner. This isn’t a big deal for the vast majority of installations, but when you have 100K+ tags, you can’t just leave this on the table unaddressed.
What I’ve done so far is to create the bulky Ignition tag historian table (sqlth_1_data) as a memory-optimized MSSQL table with schema-only durability to cut down on log bloat. Hourly I’m transferring this data to the downstream-compatible format. The view that shows the downstream-compatible data in Ignition-friendly format is still there, but has to have a name different than sqlth_1_data obviously so trending tools with default configurations aren’t going to work. Also I have to squirrel away the MO table data on reboot events so I don’t lose it.
But all this is a cruddy workaround for a weakness that pretty much every HMI vendor has - a tight and brittle connection between the HMI and the historian and an overarching assumption that pretty much anyone working on HMIs doesn’t know a thing about databases and that the table designs put forth (that will probably change over time) are going to work for everyone. Adding pre-written stored procedures with names & arguments written in stone that Ignition would call for bulk inserts and trend retrieval that one could adapt for their own needs would be a great start.