为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8.1
【操作系统】:麒麟V10
【CPU】:arm
【问题描述】*:
程序中的一个查询SQL每次执行后几秒钟,管理工具就显示断开连接,随后就发现服务已经停止了。再次启动服务后执行该查询SQL,服务就再次异常终止。
查看达梦日志,显示
sigterm_handler receive signal 11
SYSTEM SHUTDOWM ABORT
查询社区中的帖子说可能有数据损坏,目前正在用chk工具检查该数据库。随后我更换了另一台服务器上的数据库执行该SQL,问题再次出现,服务异常终止了。所以我怀疑应该没有这么巧,两台服务器都在同一批表中产生损坏。
请问有什么解决思路吗?
后面我逐步删减SQL语句进行测试,发现只要删掉查询字段中的
SINGLE_CONNSTR(T35.NAME) OVER (PARTITION BY T34.DON_CODE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FACTOR_RESULT
这一字段,就不会导致服务崩溃
是因为达梦数据库在分析函数的使用上有什么要求吗?
该聚集函数定义如下
OBJECT
(
CURRENTSTR VARCHAR2(4000),
CURRENTSEPRATOR VARCHAR2(8),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT CONNSTRIMPL)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT CONNSTRIMPL,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN CONNSTRIMPL,
RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT CONNSTRIMPL,
CTX2 IN CONNSTRIMPL) RETURN NUMBER
)
DMDBCHK跑完了,错误数量是0.
我尝试用wm_concat函数代替自定义的函数,仍然会引发服务崩溃。
查询语句比较长,帖子中不允许全部粘贴,我截其中一部分吧
SELECT DISTINCT T.ID,
T.BLD_STATION_ID,
T16.ARC_ID,
T.DON_ID,
DECODE(T.IS_DON_CODE,
1, T.DON_CODE,
NULL) AS DON_CODE,
T.EXECUTE_DATE,
T5.COLLECTION_DATE,
T5.CHECK_IS_REGULAR,
T.NAME,
T9.NAME AS SEX_NAME,
NVL(T23.NAME, T13.NAME) AS DON_TYPE_NAME,
T11.NAME AS ORG_TYPE_NAME,
T12.NAME AS ORG_UNIT_NAME,
(T.PST_ABO || T.PST_RHD) AS C_TEST_BLD_TYPE,
T5.DON_KIND_ID,
T6.NAME AS DON_KIND_NAME,
T5.VOLUME AS VOL,
(T.TEST_ABO || T.TEST_RHD) AS F_TEST_BLD_TYPE,
T15.NAME AS F_TEST_RESULT,
T.BIRTHDAY,
T17.NAME AS PLACE_NAME,
T18.NAME AS EXECUTOR_NAME,
T.HEALTH_CARD_TYPE_ID AS HEALTH_CARD_ID,
DECODE(T.HEALTH_CARD_TYPE_ID,
NULL,0,1) AS HEALTH_CARD_STR,
T20.NAME AS HEALTH_CARD,
T20.BG_COLOR AS BG_COLOR,
T20.FG_COLOR AS FG_COLOR,
T.HEALTH_CARD_IS_USED AS HC_IS_USED,
T19.CODE AS DON_CARD,
HEALTH_CARD_COUNT,
DECODE(T5.CHECK_IS_REGULAR,
1, RTRIM(TO_CHAR(T5.VOLUME, 'FM9999990.99'), '.') || T21.UNIT,
NULL) AS VOLUME_NAME,
T22.NAME AS COLLECT_NAME,
(SELECT CONNSTR(DISTINCT(TT1.NAME || ':' || TT2.NAME))
FROM T_LMT_APPS_SPECIAL_RESULT TT0
LEFT OUTER JOIN T_LMT_DEF_TEST_ITEM TT1
ON TT0.APPS_SPECIAL_ITEM_ID = TT1.ID
LEFT OUTER JOIN T_LMT_DEF_TEST_ITEM_RESULT TT2
ON TT0.APPS_SPECIAL_RESULT_ID = TT2.ID
WHERE TT0.DON_CODE = T.DON_CODE
AND TT0.CALL_BACK_LIST_ID IS NULL) AS SPECIAL_ITEM_NAMES,
SINGLE_CONNSTR(T35.NAME) OVER (PARTITION BY T34.DON_CODE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FACTOR_RESULT,
DECODE(T5.CHECK_IS_REGULAR,
1, RTRIM(TO_CHAR(T5.RMM_VOLUME, 'FM9999990.99'), '.') || 'ML',
NULL) AS RMM_VOLUME_NAME,
T5.RMM_VOLUME,
T30.NAME AS PE_EXECUTOR_NAME,
T31.PICC_RATION_TYPE,
T31.PICC_NO,
T31.PICC_ERROR_MESSAGE,
DECODE(T.IDENTITY_TYPE_ID,
0,T.IDENTITY_CODE,
NULL) AS IDENTITY_CODE,
DECODE(TT1.LINK_VALUE,
NULL, TT11.LINK_VALUE,
TT1.LINK_VALUE) CONTRACT_PHONE,
DECODE(TT5.LINK_VALUE,
NULL, TT15.LINK_VALUE,
TT5.LINK_VALUE) CONTRACT_ADDRESS,
T32.NAME AS ORG_UNIT_DONOR_NAME,
DECODE(T33.ID, NULL,0,1) AS IS_REACTION
FROM T_DNM_DONOR T
LEFT JOIN T_DNM_DONOR_CODE T16
ON T.DON_ID = T16.ID
LEFT JOIN T_DNM_DONOR_LINK_MODES TT5
ON T.ID = TT5.DONOR_ID
--以下是30多个表的join关联
;