Hi, We are trying to set up OTRS 3.0.6 with DB2 9.7 FP3a on a 64bit Linux system. We have run into a couple of issues however, and it looks like the SQL/data type sizes are not supported by DB2. When we try to view the example ticket, the following error is printed to the Apache error logs: Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048, referer: http://1.1.1.1/otrs/index.pl?Action=AgentTicketZoom;TicketID=1 The database is created with a pagesize of 32K (32768). Our DBA checked the query in question, and determined that it required a pagesize of '33529'; i.e. db2 'describe SELECT sa.ticket_id, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_reply_to, sa.a_message_id, sa.a_in_reply_to, sa.a_references, sa.a_body, st.create_time_unix, st.ticket_state_id, st.queue_id, sa.create_time, sa.a_content_type, sa.create_by, st.tn, article_sender_type_id, st.customer_id, st.until_time, st.ticket_priority_id, st.customer_user_id, st.user_id, st.responsible_user_id, sa.article_type_id, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.ticket_answered, sa.incoming_time, sa.id, st.freekey1, st.freetext1, st.freekey2, st.freetext2, st.freekey3, st.freetext3, st.freekey4, st.freetext4, st.freekey5, st.freetext5, st.freekey6, st.freetext6, st.freekey7, st.freetext7, st.freekey8, st.freetext8, st.freekey9, st.freetext9, st.freekey10, st.freetext10, st.freekey11, st.freetext11, st.freekey12, st.freetext12, st.freekey13, st.freetext13, st.freekey14, st.freetext14, st.freekey15, st.freetext15, st.freekey16, st.freetext16, st.ticket_lock_id, st.title, st.escalation_update_time, st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6, st.type_id, st.service_id, st.sla_id, st.escalation_response_time, st.escalation_solution_time, st.escalation_time, st.change_time FROM article sa, ticket st WHERE sa.ticket_id = ? AND sa.ticket_id = st.id' | cut -c23-35 | awk '{i=i+$1; print $1,i;}' | tail -n 1 33529 This is above the maximum supported by DB2 (32768). Has anyone successfully ran OTRS3 and DB2? Any suggestions on how we can overcome this problem? Thanks! Pierce CONFIDENTIALITY NOTICE: The contents of this email are confidential and for the exclusive use of the intended recipient. If you receive this email in error, please delete it from your system immediately and notify us either by email, telephone or fax. You should not copy, forward, or otherwise disclose the content of the email.
Hi Pierce, Don't run OTRS on DB2 - we use MySQL - but our other apps all use DB2, so we have experience with the problem you describe. LONG VARCHAR columns are pointers to data, and do not count a lot in DB2 when it comes to calculating the "width" of the select statement, i.e. the necessary size of your page. You might try to change a few of the columns below to LONG VARCHAR. The only caveat you might have is when searching over these columns: LIKE does not work when using a LONG VARCHAR, so it might be possible that changing the column type will have repercussions somewhere else in the OTRS code where a full text search is made. Also, a LONG VARCHAR, being a pointer, requires the database to essentially perform more than one select to fetch data, but you as a programmer do not notice this as this is handled by DB2. It does have slight impact on performance because of this, but depending on how data is searched for, one generally does not notice this. LONG VARCHARs can have a max length of 32768 characters. Regards Rudolf Bargholz -----Ursprüngliche Nachricht----- Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Ward, Pierce Gesendet: Dienstag, 8. März 2011 13:43 An: 'otrs@otrs.org' Betreff: [otrs] Does OTRS 3 support DB2? Hi, We are trying to set up OTRS 3.0.6 with DB2 9.7 FP3a on a 64bit Linux system. We have run into a couple of issues however, and it looks like the SQL/data type sizes are not supported by DB2. When we try to view the example ticket, the following error is printed to the Apache error logs: Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048, referer: http://1.1.1.1/otrs/index.pl?Action=AgentTicketZoom;TicketID=1 The database is created with a pagesize of 32K (32768). Our DBA checked the query in question, and determined that it required a pagesize of '33529'; i.e. db2 'describe SELECT sa.ticket_id, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_reply_to, sa.a_message_id, sa.a_in_reply_to, sa.a_references, sa.a_body, st.create_time_unix, st.ticket_state_id, st.queue_id, sa.create_time, sa.a_content_type, sa.create_by, st.tn, article_sender_type_id, st.customer_id, st.until_time, st.ticket_priority_id, st.customer_user_id, st.user_id, st.responsible_user_id, sa.article_type_id, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.ticket_answered, sa.incoming_time, sa.id, st.freekey1, st.freetext1, st.freekey2, st.freetext2, st.freekey3, st.freetext3, st.freekey4, st.freetext4, st.freekey5, st.freetext5, st.freekey6, st.freetext6, st.freekey7, st.freetext7, st.freekey8, st.freetext8, st.freekey9, st.freetext9, st.freekey10, st.freetext10, st.freekey11, st.freetext11, st.freekey12, st.freetext12, st.freekey13, st.freetext13, st.freekey14, st.freetext14, st.freekey15, st.freetext15, s t.freekey16, st.freetext16, st.ticket_lock_id, st.title, st.escalation_update_time, st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6, st.type_id, st.service_id, st.sla_id, st.escalation_response_time, st.escalation_solution_time, st.escalation_time, st.change_time FROM article sa, ticket st WHERE sa.ticket_id = ? AND sa.ticket_id = st.id' | cut -c23-35 | awk '{i=i+$1; print $1,i;}' | tail -n 1 33529 This is above the maximum supported by DB2 (32768). Has anyone successfully ran OTRS3 and DB2? Any suggestions on how we can overcome this problem? Thanks! Pierce CONFIDENTIALITY NOTICE: The contents of this email are confidential and for the exclusive use of the intended recipient. If you receive this email in error, please delete it from your system immediately and notify us either by email, telephone or fax. You should not copy, forward, or otherwise disclose the content of the email. --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
On 3/8/11 12:34 PM, "Rudolf Bargholz"
Hi Pierce,
Don't run OTRS on DB2 - we use MySQL - but our other apps all use DB2, so we have experience with the problem you describe. [snip] LONG VARCHARs can have a max length of 32768 characters.
I wouldn't go that far. FYI: DB/2 10 for z/OS and AIX do not have this limitation. Doesn't help in this case, but we have successfully run OTRS against both systems using v10 of DB/2.
Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048, referer: http://1.1.1.1/otrs/index.pl?Action=AgentTicketZoom;TicketID=1
To work around this in v9, make sure you have the fixes listed here applied: https://www-304.ibm.com/support/docview.wss?uid=swg1JR30604 And make sure you do: db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD,NO_HVCHECK_ALL That should allow it to work with DB/2 v9 LUW. (BTW, if you do have access to a z/OS DB/2 and a ZIIP, OTRS *rocks*. Most of the queries are ZIIP-eligible.).
Thanks a lot for the information, and sorry for the slow response.
To work around this in v9, make sure you have the fixes listed here applied: https://www-304.ibm.com/support/docview.wss?uid=swg1JR30604 And make sure you do: db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD,NO_HVCHECK_ALL That should allow it to work with DB/2 v9 LUW.
I passed this information on to our DBA, and unfortunately it did not make any difference for us. However, when he learned that this was a sorting problem, he did the following: db2 create index article_ct_idx on "article(create_time, id)" This solved our issue as there is no need to sort with that index created. We also spotted another incompatibility from the default SQL files. From the file 'otrs-initial_insert.db2.sql', the following INSERT fails with this error: INSERT INTO article_plain (article_id, body, create_by, create_time, change_by, change_time) ... SQL0408N A value is not compatible with the data type of its assignment target. Target name is "BODY". SQLSTATE=42821 We have also managed to work around this, by adding "cast()" around the body value, but presumably this will also need to be changed in 'Kernel/System/Ticket/ArticleStorageDB.pm'. I'm not sure if there is any other, more portable way to get around this. Regards, Pierce CONFIDENTIALITY NOTICE: The contents of this email are confidential and for the exclusive use of the intended recipient. If you receive this email in error, please delete it from your system immediately and notify us either by email, telephone or fax. You should not copy, forward, or otherwise disclose the content of the email.
participants (3)
-
David Boyes -
Rudolf Bargholz -
Ward, Pierce