Total Pageviews

2017/08/11

[DB2] Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null

Problem

When I using Apache Commons DBUtils to do batch insert data into IBM DB2, I get this SQLException:
1
2
3
SQL Message: [jcc][t4][102][10040][4.14.88] Batch failure.  
The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null


Code Snippet:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    /**
     * 批次處理.
     * 
     * @param sql
     *            insert/update/delete sql statemet
     * @param params
     *            parameters
     * @throws SQLException
     *             in case of fail to do insert/update/delete
     */
    public void batchUpdate(String sql, Object[][] params) throws SQLException {
        QueryRunner run = new QueryRunner();
        run.batch(this.getConnection(), sql, params);
    }



How-To
According to the troubleshooting information from IBM Support, it said:
The error is caused by the DB2 Transaction log for the Data Warehouse becoming full and not able to process the full batch operation.


But the root case is "value too large" in insert SQL statement, having nothing to do with DB2 Transaction log.   

2017/08/10

How to Improve Code Coverage After Using Apache Lombok?

Problem
After using Project Lombok, we can find out it help use reduce our boilerplate code. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package albert.practice.lambda;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import com.google.common.base.Strings;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

public class LambdaTest {

    public List<Person> filterData() {
        List<Person> people = createData();
        return people.stream().filter(p -> p.age >= 35).filter(p -> !Strings.isNullOrEmpty(p.email))
                .collect(Collectors.toList());
    }

    public List<Person> filterDataWithSort() {
        List<Person> people = createData();
        return people.stream().filter(p -> p.age >= 35).sorted((p1, p2) -> (p1.age - p2.age))
                .sorted((p1, p2) -> p2.name.compareTo(p1.name)).collect(Collectors.toList());
    }

    public List<Person> createData() {
        Person ben = new Person("Ben Whittaker", 70, "ben@gmail.com");
        Person jules = new Person("Jules Ostin", 30, "jules@gmail.com");
        Person fiona = new Person("Fiona Farwell", 68, null);
        Person matt = new Person("Matt", 35, "matt@gmail.com");
        
        return Arrays.asList(ben, jules, fiona, matt);
    }

    @Data
    @ToString
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Person {
        private String name;
        private int age;
        private String email;
    }

}

But you will also notice your code coverage rate is down dramatically.


How to improve the code coverage rate?


How-To
Add two dependencies to your pom.xml
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<dependencies>
    <dependency>
        <groupId>org.meanbean</groupId>
        <artifactId>meanbean</artifactId>
        <version>2.0.2</version>
    </dependency>

    <dependency>
        <groupId>nl.jqno.equalsverifier</groupId>
        <artifactId>equalsverifier</artifactId>
        <version>2.2.1</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Check the testPersonBean() and testEqualsAndHashCode() in this test case:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package albert.practice.lambda;

import static org.junit.Assert.assertEquals;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.meanbean.test.BeanTester;

import albert.practice.lambda.LambdaTest.Person;
import lombok.extern.slf4j.Slf4j;
import nl.jqno.equalsverifier.EqualsVerifier;
import nl.jqno.equalsverifier.Warning;

@Slf4j
public class LambdaUnitTest {

    private LambdaTest lambdaTest = null;

    @Before
    public void setup() {
        lambdaTest = new LambdaTest();
    }

    @Test
    public void testCreateData() {
        List<Person> people = lambdaTest.createData();
        assertEquals(people.size(), 4);
    }

    @Test
    public void testFilterData() {
        List<Person> people = lambdaTest.filterData();
        log.debug("testFilterData = " + people.toString());
        assertEquals(people.size(), 2);
    }

    @Test
    public void testFilterDataWithSort() {
        List<Person> people = lambdaTest.filterDataWithSort();
        log.debug("testFilterDataWithSort = " + people.toString());
        assertEquals(people.size(), 3);
    }

    // Testing getter and setters methods
    @Test
    public void testPersonBean() {
        new BeanTester().testBean(LambdaTest.Person.class);
    }

    // Testing equals() and hashCode()
    @Test
    public void testEqualsAndHashcode() {
        EqualsVerifier.forClass(LambdaTest.Person.class)
                .suppress(Warning.STRICT_INHERITANCE, Warning.NONFINAL_FIELDS).verify();
    }

}

After running code coverage analysis, you can find out this problem had been solved:




Reference
[1] http://www.jdev.it/tips-unit-testing-javabeans/

2017/08/09

[Squirrel SQL Client] Is it possible to have Squirrel SQL auto-resize the column width of the result set?

Problem
Is it possible to have Squirrel SQL auto-resize the column width of the result set? 


How-To
Steps:
(1) move your mouse to query result grid
(2) right click on query result grid
(3) check "Always adjust all column widths"



2017/08/08

[Tools] [Text Compare] How to Compare Two Lengthy JSON String

Problem
I have two lengthy JSON String, if I would like to compare the difference between the two JSON string, how to do it?

How-To
Here are the steps to fulfill this requirement:
(1) connect to https://text-compare.com/
(2) copy first JSON string to left hand side
(3) copy second JSON string to right hand side
(4) click Compare button

(5) check the difference

2017/08/07

[SQL Server] How to find similar table / column name by SQL

I am using Microsoft SQL Server...

If I would like to get a list of table name which table name has %arap%, you can use this SQL statement to do query:
SELECT NAME 
FROM   sys.tables 
WHERE  NAME LIKE '%arap%' 



If I would like to get a list of table which has column name %apply%, you can use this SQL statement to do query:
SELECT t.NAME AS 'TableName', 
       c.NAME AS 'ColumnName' 
FROM   sys.columns c 
       JOIN sys.tables t 
         ON c.object_id = t.object_id 
WHERE  c.NAME LIKE '%apply%' 
ORDER  BY t.NAME, 
          c.NAME;