Thanks. Yes it is IBM. I will open a ticket.
Original Message:
Sent: Thu October 24, 2024 08:00 AM
From: Bartosz Marchewka
Subject: How to query work order long description field with SQL?
Hi,
If IBM is maintaining your database then yes I suggest you to create ticket and describe issue.
If not then, I think, you need to start discussion with the team that is responsible for your DB2.
------------------------------
Bartosz Marchewka
IBM Maximo Consultant
AFRY
Original Message:
Sent: Thu October 24, 2024 07:53 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?
Yup that is it Bartosz. Is this something I should submit a ticket on?
------------------------------
Christopher Stewart
Original Message:
Sent: Thu October 24, 2024 07:49 AM
From: Bartosz Marchewka
Subject: How to query work order long description field with SQL?
Hi @Christopher Stewart,
I assume that your Maximo (Manage) is using DB2. Could you please check if you are getting this king of error message when you are executing select statement:
SQL Error: [jcc][t4][10379][11961][4.31.10] Client disconnect exception encountered: The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database.Reason code = "3".. SQLCODE=-1773, SQLSTATE= , DRIVER=4.31.10 ERRORCODE=-4499, SQLSTATE=nul
If yes then problem is not with the SQL Client as I thought but with DB2 setup and size od data.
We had similar issue with one of our customer and we received this feedback from IBM support:
"
XML and large object (LOB) data must be inline to be successfully queried.
INLINE-LOBs: The data for an INLINE LOB column is contained in the base table. This feature is enabled through the INLINE LENGTH option on the CREATE TABLE statement or the ALTER TABLE statement. The limit for inline lobs is 32,673 bytes.
(...)
Please find information about inline lobs :
https://www.ibm.com/docs/en/db2/11.5?topic=performance-inline-lobs-improve
https://www.ibm.com/docs/en/db2/11.5?topic=tables-storing-lobs-inline-in-table-rows
https://www.ibm.com/docs/en/db2/11.5?topic=aracp-admin-is-inlined-determine-if-data-is-inlined
"
------------------------------
Bartosz Marchewka
IBM Maximo Consultant
AFRY
Original Message:
Sent: Thu October 24, 2024 07:41 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?
Ahh I did not catch that. Thanks! Do you have a SQL client you recommend?
------------------------------
Christopher Stewart
Original Message:
Sent: Wed October 23, 2024 03:33 PM
From: Bartosz Marchewka
Subject: How to query work order long description field with SQL?
Hi @Christopher Stewart
Please observe that in the result you have an error (please see the screen below). I recognised that issue and it's connected with SQL client and data in LDTEXT. Please execute for example statement SELECT COUNT(*) FROM LONGDESCRIPTION
Please try to use another SQL Client.
------------------------------
Bartosz Marchewka
IBM Maximo Consultant
AFRY
Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?
Yes I am just doing select * from longdescription. I was wondering if they changed this with MAS 8 vs 7.6 or something?
------------------------------
Christopher Stewart
Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?
Howdy,
Is the query you are using just SELECT * FROM longdescription and this only returns 6 rows?
That would not make sense if you are saying you have 32,000 workorders and all of them have a populated long description (ldtext).
Can you share the current SQL you are using that returns the 6 rows please.
Daz
------------------------------
Darren Hornidge
Asset Care Systems Manager
CCEP
Melbourne VIC
Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?
Thanks for the suggestion. This is what is confusing to me. I only see 6 records in my LONGDESCRIPTION table where I have 32,000 work orders with long descriptions. Where are the DESCRIPTION_LONGDESCRIPTION field values stored?
------------------------------
Christopher Stewart
Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?
Hi Chris,
If I understand correctly, you just want to some SQL to find workorders where the long description field contains some text?
If you are doing this against the database, you could use something like this. In my example, I am looking for the text OIL
SELECT wonum, description, ldtext
FROM WORKORDER
INNER JOIN longdescription ON longdescription.ldkey = workorder.workorderid and ldownertable = 'WORKORDER'
WHERE ((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = '21')
and UPPER(longdescription.ldtext) LIKE '%OIL%'
Or, if you want to do this from the where clause in work order tracking, you could try this
((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = '21')
and EXISTS (SELECT 1
FROM longdescription
WHERE longdescription.ldkey = workorder.workorderid
and ldownertable = 'WORKORDER'
and UPPER(longdescription.ldtext) LIKE '%OIL%'
)
Hopefully this helps you
Daz
------------------------------
Darren Hornidge
Asset Care Systems Manager
CCEP
Melbourne VIC
Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?
Maximo 8.11, I am trying to query work orders and include the long description field. I see there is a table LONGDESCRIPTION but it doe snot look to contain any of the long description data, only what types of entities have the long description field. Is this possible? If so how is it done?
------------------------------
Christopher Stewart
------------------------------