Composite (JSON) Parameter?
Is it possible to evaluate a pipeline parameter that is itself a JSON document?
I am finding a number of use cases for this, especially when using sub-pipelines and you need the input parameters to...
Forum Discussion
OH MAN! This brings back memories! You might have fallen into a trap I consider a design flaw! The default primary key for this variant of sybase, IIRC, was CLUSTERED! Is that what it is on YOUR system?
If so, you must do things IN KEY ORDER! If you do them OUT of order, it will take LONGER!
How much longer? Well, I was on a contract where I often had to run things at the end of the week just before I flew out. NO PROBLEM! It was a reliable process, and generally took 30 MINUTES to run!
Well, sometimes I would get to the airport and be struck with HORROR! I would suddenly remember that the DBA might make a change that week. Whenever he “made a change”, he would drop and recreate the table, WITH NO SPECIFICATION! So the primary key would be clustered, and the routine I was running would take about THREE DAYS!!!
During this period, the connection will APPEAR to hang! It is really just waiting for the operation to complete.
CLUSTERED on SYBASE, at least with the first variant they had, AND with M/S SQL Server up to 6.5(or was it 7), since they shared the same code. Was GREAT for READING, since it was faster and took less space. It was great for writing, IN SEQUENCE! It was HORRIBLE for writing OUT of sequence. The delay on a particular record is based on where the update or delete occurs. If it is towards the end, it might not be very noticeable. If it is at the beginning, it could take a LONG time!
Of course it is ALSO possible that you simply have a lock, or a deadlock. If it is a lock, it will likely eventually go away. If it is a deadlock, it will stay locked for a while and eventually kill, iirc, the longest running transaction. Of course the transaction on the sacrificed job will roll back also.
Stephen:
Thanks for the feedback!
First of all, exactly what do you mean by “do things” in key order?
Here’s some more background; hoping this will be helpful!
In this case, we are issuing SELECT statements that involve joining several tables.
To expand on my earlier statement of the queries otherwise being “just fine”, these queries normally complete very quickly regardless of where it’s being run from: SnapLogic “Generic JDBC Execute” Snap, or a tool like “averSQL”, or Sybase’s own “Sybase Central.”
The query hangs only in SnapLogic, and it does not do it every time.
We have yet to be able to reproduce it on demand; it’s sporadic.Note that averSQL uses Windows-native ODBC, not JDBC, and Sybase Central uses the Sybase-native connection API. That makes me suspect the problem is driver-specific. A few days ago, I found a comment elsewhere online where someone using the JConnect (jconn4.jar) driver complained about similar behavior, and Sybase recommended using the (newer) “iAnywhere” JDBC-ODBC bridge drivers. But… (a) I haven’t been able to re-find the comment, and (b) we haven’t been able to get the bridge drivers working on our GroundPlex. (For some reason it can’t find the dbjodbc9.dll file.)
We suspect that the hangs happen when the queries involve one particular table, which is a change log. The change log table has NO primary key defined! (I know: bleah!) It does have 1 unique index (on 5 columns), and 3 other non-unique indexes (one on 4 columns, two others on two columns… hm… and the two others seem to be identical: wow, what’s up with that?)
I think that a deadlock is extremely unlikely, as we’re experiencing this in testing where the only two processes accessing the database are our application (1 QA user) and the SnapLogic pipelines themselves. The QA person makes some changes with the application, then kicks off the pipelines, then checks our central database to see if certain changes have taken place in the data there.
To the best of my knowledge, most if not all of the other tables have PKs. If I can reproduce the problem, I’ll post more details regarding the actual query and the specs of the tables involved. Maybe we can find a problem hiding in those details.
Second, I’m not sure how to find the defaults for Sybase.
I’ve worked with MS SQL Server, Oracle, PostgreSQL, MySQL, and MaxDB… but it’s been over 20 years since I’ve touched Sybase, so I’m essentially approaching it anew. How would I check to see if it is defaulting to clustered? Does it still matter since the potentially problematic table has no PK?
