귀찮은건 질색인 개발자의 공간: SQL Server

레이블이 SQL Server인 게시물을 표시합니다. 모든 게시물 표시

2021년 4월 30일 금요일

Apache Zeppelin에서 jdbc 연동시 조회할때 TLS 관련 오류 발생

0. 현재 개발환경

- OS : Ubuntu 20.4

- Main DB : SQL Server on Linux 2019 Express

- 보조 DB : SQL Server 2008, SQL Server 2014

- Java : Apache Zeppelin 권장 버전 (OPENJDK 1.8)

1. 보다시피 버전차이가 엄청 남.

2. Zeppelin에 jdbc (9.2.1) 연결을 했을때, SQL Server 2014는 아무 문제가 없음

3. SQL Server 2008에 연결시 TLS 버전을 지원 안한다는 오류 발생함.

4. 해당 서버 버전의 TLS 1.0 프로토콜이 너무 오래되서 지원을 안함.

5. 일단.. 해당 서버의 OS인 Windows 2008 R2에 TLS 1.1 / TLS 1.2 설정을 해줌.

6. 동일 에러 발생 - SQL Server 2008 자체에서 TLS 1.0 통신만 지원하는 것으로 판단.

7. jdbc 4.2를 쓰면 된다는 개소리 믿고 삽질하다가.. 다음과 같은 글 발견..

https://blog.naver.com/PostView.nhn?blogId=hgp33&logNo=221293449287&from=search&redirect=Log&widgetTypeCall=true&directAccess=false

8. 뭐 내용은 Java jdbc 보안문제로 동일함.

9. 문득 깨닮음을 얻은 나는 Ubuntu의 JRE에 있는 security 설정을 건들면 되겠다는 생각이 듬.

10. 저 링크에는 싹 지우라고 했지만... 글 자체가 3년이 넘은지라..

내 목적인 TLS 관련 부분만 지움..

jdk.tls.disabledAlgorithms 에서 TLS 1.0, TLS 1.1을 지워주자..

11. 당근 바로 안된다.. 서버 재부팅!!!

12. 해결!

13. 끗! 뭘봐!

2018년 10월 31일 수요일

SQL 조건절에 CASE 문으로 여러 조건 결합하기...

오늘의 난관..

SQL 쿼리에서 기존에 프로시저를

8개 매장별로 조회하게 만들었는데..

오늘 요건이 인터넷 매장은 따로 보고...

전체 매장을 선택하면 인터넷 매장을 제외한 나머지들의 합산만 보고 싶다고 하여..

궁리...

프로시저에 임시테이블을 써서.. 분기문으로 나눌수가 없음..

조건절에서 제어해야 할 거 같아서.. 이리저리 구글링해 보니..

CASE WHEN을 OR로 연결해서 쓸 수 있다는 걸 알게 됨..

5000 이란 코드가 들어오면.. 인터넷 매장 (5040)을 제외한 나머지를 조회 시키고..

해당 매장코드가 들어오면 정상조회를 시키는 쿼리를 결국 완성..

AND
(
(
CASE WHEN @LOC = '5000' THEN '5010' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5020' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5030' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5060' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5070' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5080' END = 매장코드컬럼
OR
CASE WHEN @LOC = '5000' THEN '5090' END = 매장코드컬럼
)
OR 매장코드컬럼 LIKE ''+ @LOC + ''
)

또 쿼리 철인가... 쿼리만.. 죽어라 하게 되네...

======================================

2020-12-02 : 좀 나중에 덧붙임..

이렇게 쓰면.. 쿼리 처리는 쉽겠지만.. 성능은 포기해야 할지도..

지금 다시 짜라고 하면.. @LOC 별로 분기를 만들어서.. 귀찮지만(!!)

각각에 해당하는 쿼리를 짜는 것을 선택할 듯...

2018년 8월 21일 화요일

Using SQuirreL SQL Client With OpenJDK 10.X

Java가 유료화 된다는 소식을 접하고..

나중에 생길수도 있는 불필요한 문제를 피하고자 OpenJDK로 갈아탔다..

요즘엔 Java 개발할 일이 없어서 일단... 환경설정만 OpenJDK 10.2 버전에 맞게 설정했음.

그런데!!!

두둥!!!!

Squirrel SQL Client 3.7.X 버전이 니가 JRE를 10 버전 이상 쓰고 있다고 안된다고 오류 발생.

OpenJDK 버전을 상위버전에서 지원할까 하여 3.8.1 버전 다운로드해서 설치했지만..

여전히 같은 오류 발생...

로그를 봤을때는 JRE 버전체크를 통과 못하는 것으로 보인다..

실행 배치 파일을 열어서 확인..

다음과 같은 줄이 보였다..

@REM "%LOCAL_JAVA%" -cp "%SQUIRREL_SQL_HOME%\lib\versioncheck.jar" JavaVersionChecker 1.8 9

@REM는 내가 주석처리 한 거임.. 위의 명령 대신...

"%LOCAL_JAVA%" -cp "%SQUIRREL_SQL_HOME%\lib\versioncheck.jar" JavaVersionChecker 10

요렇게 바꿔주니까 통과!!

그런데.. 기존 설정에서 MS-SQL JDBC 드라이버를 6.2 버전쓰고 있었는데..

이번엔 Class가 없다는 오류가 발생..

역시 요딴거에 패치가 있을듯 하여.. 검색하니..

https://docs.microsoft.com/ko-kr/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

에서 7.0 버전 발견..

설치해 보니.. JRE 8 버전과 JRE 10 버전 두가지 드라이버가 있드라..

그래서.. 10버전으로 드라이버 교체해서 성공!!

끗!

2017년 7월 4일 화요일

Java로 SQL Agent Log에서 잡다한 거 빼고 보기

0. 일단.. SQL Server Profiler 한 바퀴 돌려주고

1. xml로 저장해서 java로 parsing 한다.

2. 내 맘대로 짠거임.. 알아서 변용하도록

3. 남자답게!! 출처는 밝히자!!

package com.lifebook.util;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class parseSqlLog {
 private static Connection conn = null;
 private static PreparedStatement psmt = null;
 private static ResultSet rs = null;
 private static String conUrl = "니꺼 jdbc 접속 문자열!";
 private static String[] chkFilterList = {
  "SQL:BatchCompleted",
  "Trace Stop",
  "RPC:Completed",
  "select @@microsoftversion",
  "select convert(sysname, serverproperty(N'servername'))",
  "SET TEXTSIZE 1024",
  "SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())",
  "SELECT N'Testing Connection...'",
  "SET NO_BROWSETABLE ON",
  "SET NO_BROWSETABLE OFF",
  "set fmtonly off",
  "select serverproperty(N'instancename')",
  "ServerProperty('",
  "SERVERPROPERTY",
  "@@microsoftversion",
  "#err_log_tmp er",
  "SERVERPROPERTY(N'",
  "SET LOCK_TIMEOUT",
  "EXEC master.dbo.xp_readerrorlog",
  "CAST(serverproperty(N'Servername') AS sysname)",
  "DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname)",
  "select value_in_use from sys.configurations where configuration_id",
  "select is_srvrolemember('sysadmin') * 1 +is_srvrolemember('serveradmin') * 2 +is_srvrolemember('setupadmin') * 4 +is_srvrolemember('securityadmin') * 8 +is_srvrolemember('processadmin') * 16 +is_srvrolemember('dbcreator') * 32 +is_srvrolemember('diskadmin') * 64+ is_srvrolemember('bulkadmin') * 128",
  "select SERVERPROPERTY(N'servername')",
  "EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xC8FAC217480CAB478E3BB07AB02962E9",
  "BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome"
 };

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  try {
   File fXmlFile = new File("E:\\workspace\\parseSqlLog\\src\\com\\lifebook\\util\\20170703.xml");
   DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
   DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
   Document doc = dBuilder.parse(fXmlFile);
   doc.getDocumentElement().normalize();
   System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
   System.out.println("----------------------------");
   NodeList nList = doc.getElementsByTagName("Events");
   double rowCnt = 0d;
   for (int temp = 0; temp &lt; nList.getLength(); temp++) {
    System.out.println("item:::" + nList.item(temp).getChildNodes().getLength());
    Node nNode = nList.item(temp);
    NodeList cList = nNode.getChildNodes();
    for(int cCnt = 0 ; cCnt &lt; cList.getLength() ; cCnt ++){
     Node cNode = cList.item(cCnt);
     if (cNode.getNodeType() == Node.ELEMENT_NODE) {
      Element eElement = (Element) cNode;
      if(chkString(eElement.getAttribute("name").toString())){
       //System.out.println("name : " + eElement.getAttribute("name"));
       NodeList colsList = cNode.getChildNodes();
       for(int colCnt = 0 ; colCnt &lt; colsList.getLength() ; colCnt ++){
        Node colNode = colsList.item(colCnt);
        //System.out.println(colNode.getNodeName());
        if (colNode.getNodeType() == Node.ELEMENT_NODE) {
         Element colEle = (Element) colNode;
         //System.out.println("cols name : " + colEle.getAttribute("name"));
         if(colEle.getAttribute("name").toString().equals("TextData")){
          if(chkString(colEle.getTextContent())){
           rowCnt ++;
           //System.out.println(rowCnt + "::::::" + colEle.getTextContent());
           //System.out.println(colEle.getTextContent().toLowerCase().indexOf("select"));
           chkDbCmd(colEle.getTextContent());
           if(colEle.getTextContent().toLowerCase().indexOf("select")&gt;=0){
            //System.out.println(rowCnt + "::::::" + colEle.getTextContent());
            //chkDbCmd(colEle.getTextContent());    
           }
          }          
         }
        }
       }
      }
     }
    }
   }
  } catch (ParserConfigurationException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SAXException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public static boolean chkString(String chkStr){
  boolean rtnBool = true;
  for(int chkCnt = 0 ; chkCnt &lt; chkFilterList.length ; chkCnt++){
   //System.out.println(chkStr);
   //System.out.println(chkFilterList[chkCnt]);
   //System.out.println(chkStr.indexOf(chkFilterList[chkCnt]));
   if(chkStr.indexOf(chkFilterList[chkCnt]) &gt;= 0){
    rtnBool = false;
    break;
   }
  }
  return rtnBool;
 }
 
 public static void chkDbCmd(String sql){//얘는 SQL 실행시 오류 발생하는지 알아볼라고 만든거임..
 try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   conn = DriverManager.getConnection(conUrl);
   psmt = conn.prepareStatement(sql);
   rs = psmt.executeQuery();
   
   rs.close();
   psmt.close();
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println(sql);
   e.printStackTrace();
  } finally {
   if (conn != null) {
    try {
     conn.close();
    } catch (Exception e) {
    }
   }
   
   if (psmt != null) {
    try {
     psmt.close();
    } catch (Exception e) {
    }
   }
   
   if (rs != null) {
    try {
     rs.close();
    } catch (Exception e) {
    }
   }
  }
 }
}

SQL AGENT 에서 Application Name 으로 프로시저 찾기

먼저 하단 함수 생성

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (
   @program_name nvarchar(128)
)
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @start_of_job_id int
SET @start_of_job_id = CHARINDEX('(Job 0x', @program_name) + 7
RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(
      SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) + 
      SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 16, 4) + '-' +
      SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)
   ELSE NULL
   END
END --FUNCTION

에이전트 로그에서

SELECT *
FROM msdb.dbo.sysjobs
WHERE
job_id = dbo.GetJobIdFromProgramName ('SQLAgent - TSQL JobStep (Job 0xC8FAC217480CAB478E3BB07AB02962E9 : Step 2) ')

같이 검색하면 짜잔~~! 나옴.

이거 땜시로 오늘 완전 개고생 함..

귀찮은건 질색인 개발자의 공간