P_W999

Because life has no try-catch


Leave a comment

Eclipse RCP Dynamic popup menu

I do not have a lot of experience with the Eclipse Rich Client Platform (RCP), yet I was asked to create a dynamic submenu for an element in a JFace TreeView. The content of the submenu should be filled in depending on the selected object and a fixed menu item to allow a manual search should be added too. The complete menu should be hidden if the selected object already has a value for ‘myProperty’.
I’ve struggled for days to get this working, especially since most things on the internet refered to the MenuManager which didn’t work at all in my case.

Fortunately, after some trial and error I managed to set up the submenu using the standard plugin extensions.

The fixed command

First thing to define is the fixed command which will open a new dialog.

Under the org.eclipse.ui.commands extention point, add the following command definition

      <command defaultHandler="be.x.y.z.AddToHandler" id="be.x.y.z.addTo" name="Add to ..">
      </command>

The corresponding handler class is fairly simple, it’ll just open a new dialog while passing the selected item to this dialog.

import org.eclipse.core.commands.ExecutionEvent;
import org.eclipse.core.commands.ExecutionException;
import org.eclipse.core.commands.IHandler;
import org.eclipse.core.commands.IHandlerListener;
import org.eclipse.jface.viewers.ISelection;
import org.eclipse.ui.handlers.HandlerUtil;



public class AddToHandler implements IHandler {

  @Override
  public void addHandlerListener(final IHandlerListener handlerListener) {
  }

  @Override
  public void dispose() {
  }

  @Override
  public Object execute(final ExecutionEvent event) throws ExecutionException {
    ISelection selection = HandlerUtil.getActiveWorkbenchWindow(event).getActivePage().getSelection();
    MyObject myObject = SelectionEval.singleton(selection, MyObject.class);
    MySearchDialog gds = new MySearchDialog(HandlerUtil.getActiveWorkbenchWindow(event).getShell(), myObject);
    gds.open();
    return null;
  }

  @Override
  public boolean isEnabled() {
    return true; // set to true to make it always visible
  }

  @Override
  public boolean isHandled() {
    return true; // set to true to make it always visible
  }

  @Override
  public void removeHandlerListener(final IHandlerListener handlerListener) {
  }

}

Testing the selected object

Next step is to define a property tester which will check if the selected object satisfies all conditions to show the menu.

The following property tester should be defined in the org.eclipse.core.expressions.propertyTesters extension point:

      <propertyTester class="be.x.y.z.addToPropertyTester" id="be.x.y.z.addToPropertyTester" namespace="be.x.y.z.addToPropertyTester" properties="myProperty" type="java.lang.Object">
      

The property tester itself is fairly simple and does nothing but a null-check


import org.eclipse.core.expressions.PropertyTester;
import be.x.y.z.MyObject;

public class AddToPropertyTester extends PropertyTester {

  @Override
  public boolean test(final Object receiver, final String property, final Object[] args, final Object expectedValue) {
    if (property.equals("myProperty") && receiver instanceof MyObject) {
      MyObject myObject = (MyObject) receiver;
      return myObject.getMyProperty() == null;
    }
    return false;
  }

}

Adding menu items dynamically

Adding items using a dynamic menu requires an instance of a ContributionItem.

import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeMap;
import org.eclipse.jface.action.ContributionItem;
import org.eclipse.jface.viewers.ISelection;
import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.widgets.Menu;
import org.eclipse.swt.widgets.MenuItem;
import org.eclipse.ui.PlatformUI;


public class AddToContributionItem extends ContributionItem {

  @Override
  public void fill(final Menu menu, final int index) {
    super.fill(menu, index);
    ISelection is = PlatformUI.getWorkbench().getActiveWorkbenchWindow().getActivePage().getSelection();
    MyObject singleton = SelectionEval.singleton(is, MyObject.class);
    if (singleton == null) {
      return;
    }

    Map<string, map<string,="" object="">> data = new TreeMap<>();
    // Some logic to fill in data

    int count = 0;
    for (Entry<string, map<string,="" object="">> entry : data.entrySet()) {
      if (++count > 15) {	// Limit number of items to 15
        return;
      }
      MenuItem menuItem = new MenuItem(menu, SWT.NONE, index);
      menuItem.setText(entry.getKey());
      menuItem.setData("OBJECT_DATA", entry.getValue().get("OBJECT_DATA"));	// you can store extra data here, which you can use in the selection listner
      menuItem.addSelectionListener(new SelectionAdapter() {

        @Override
        public void widgetSelected(final SelectionEvent e) {
          MyData dO = (MyData) e.widget.getData("OBJECT_DATA");
          // Whatever you want to do
        }
      });
    }

  }
}

Glueing it all together

Last step is to glue everything together in an org.eclipse.ui.menus extension point:

      <menuContribution allPopups="false" locationURI="popup:org.eclipse.ui.popup.any"> <!-- This is the menu contribution for the "add to" menu --></pre>
<menu id="be.x.y.z.addtomenu" label="Add to"> <!-- This is the menu item which <span class="hiddenSuggestion" pre="which " data-mce-bogus="1">contains</span> a sub menu -->

            	<!-- This is the dynamic part of the menu -->
            <command id="be.x.y.z.addToMenuContribution" label="..."></command>	<!-- This command will open the search dialog -->
                <!-- Use the isHandled() and isEnabled() method to show or hide this fixed menu item) -->

            	<!-- This is the fixed menu item which opens a new search dialog -->
            	<!-- Only show the menu if *selection*.count == *1* AND *selection*[O] instanceof MyObject AND *selection*[O] has no myProperty -->

                         <!-- Important, this is the property tester namespace + property -->

                  	<!-- Make sure we have selected only 1 single object -->

         </menu>
<pre>

             
   

Disclaimer: I’m not an Eclipse RCP expert and even though this might work, it’s probably not the most efficient way to do this, especially because we’re binding it to popup:org.eclipse.ui.popup.any, however, it was to only way I could get this working in our somewhat bloated project.


Leave a comment

Automating JMX calls using JMXTerm

On the project I’m currently working on, we have a lot of tasks which are exposed through JMX and which we use very often. Some tasks need to be executed on a daily base, some even more often. As we all know, repetitive task are the ones we should automate and so I did.

 

 

Since I didn’t feel like writing another Java application just to call a Java application over JMX, I did some searches for a good command line JXM application and so I found JMXTerm. After fiddling around a bit, I wrote the following script which will connect to an Oracle XE database, retreive some data and use this data to call a JMX method which accepts a couple of parameters (many of which are just 0).

 

 

# prepare Oracle connection
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

# Find the exact bean name
BEAN="$(java -jar jmxterm-1.0-alpha4.jar s -l service:jmx:rmi:///jndi/rmi://localhost:2000/myjmx -n -i discover | grep "process=MyBeanProcess,type=MyProcess")"
echo $BEAN

# Variable I'll use to query the database
BPS="40706 42001 41038 40705 40186"
for bp in $BPS; do
        # send commands to sqlplus, spool results to a file output.out
        echo "SPOOL output.out;
        SELECT REF_ID FROM MYTABLE;
        SPOOL OFF;
        exit;" | sqlplus sys/oracle@localhost as SYSDBA

        # extract the id's from the (human readable) output
        DATA=$(cat output.out | egrep "^[ ]{5}[0-9]+" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')

        # create an input script for jmxterm
        echo bean $BEAN > tmpJmxScript
        for x in $DATA; do
                echo run myJmxMethod 0 "$bp" 0 0 "${x}" 0 >> tmpJmxScript
        done;

        # connect to JMX server and execute the previously generated script
        java -jar jmxterm-1.0-alpha4.jar s -l service:jmx:rmi:///jndi/rmi://localhost:2000/myjmx -i tmpJmxScript -n
done;

The tmpJmxScript file will look something like this

bean Processes:name=MyBeanProcess,Id=2,process=MyBeanProcess,type=MyProcess
run myJmxMethod 0 40186 0 0 15907 0
run myJmxMethod 0 40186 0 0 16340 0
run myJmxMethod 0 40186 0 0 16496 0

This script tells JMXTerm to use the bean ‘MyBeanProcess’ and execute the myJmxMethod JMX method using the parameters that follow.


Leave a comment

javax.xml.ws.soap.SOAPFaultException: Fault occurred while processing.

Last week, completely out of the blue I got faced with the following Soap exception:

 

2015-05-21 14:33:38,649 ERROR http-apr-8080-exec-4 [xxx.web.upload.generic.ExceptionHandler] Soap error
javax.xml.ws.soap.SOAPFaultException: Fault occurred while processing.
 at org.apache.cxf.jaxws.JaxWsClientProxy.invoke(JaxWsClientProxy.java:156)
 at com.sun.proxy.$Proxy231.geefExcelLijst(Unknown Source)
 at xxx.web.upload.controller.UploadController.downloadBestand(UploadController.java:170)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:606)
 at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:743)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:672)
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82)
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:919)
 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:851)
 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
 at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at xxx.web.beheer.shared.util.CspFilter.doFilter(CspFilter.java:50)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at xxx.security.openam.spring.security.OpenAMSSOLogoutFilter.doFilter(OpenAMSSOLogoutFilter.java:69)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
 at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
 at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
 at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
 at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
 at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2466)
 at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2455)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.cxf.binding.soap.SoapFault: Fault occurred while processing.
 at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.unmarshalFault(Soap11FaultInInterceptor.java:75)
 at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:46)
 at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:35)
 at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:263)
 at org.apache.cxf.interceptor.AbstractFaultChainInitiatorObserver.onMessage(AbstractFaultChainInitiatorObserver.java:105)
 at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:69)
 at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:34)
 at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:263)
 at org.apache.cxf.endpoint.ClientImpl.onMessage(ClientImpl.java:771)
 at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponseInternal(HTTPConduit.java:1604)
 at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponse(HTTPConduit.java:1489)
 at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.close(HTTPConduit.java:1397)
 at org.apache.cxf.transport.AbstractConduit.close(AbstractConduit.java:56)
 at org.apache.cxf.transport.http.HTTPConduit.close(HTTPConduit.java:644)
 at org.apache.cxf.interceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor.handleMessage(MessageSenderInterceptor.java:62)
 at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:263)
 at org.apache.cxf.endpoint.ClientImpl.doInvoke(ClientImpl.java:519)
 at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:449)
 at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:352)
 at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:304)
 at org.apache.cxf.frontend.ClientProxy.invokeSync(ClientProxy.java:88)
 at org.apache.cxf.jaxws.JaxWsClientProxy.invoke(JaxWsClientProxy.java:134)
 ... 72 more

I didn’t matter whether I ran my calls through Soap-UI or through the application, I would always face this error. No clear solutions were available on the internet, except for a bunch of things you could try.

Eventually I started debugging my code again but instead of stopping once my webservice method returned, I kept going deeper, passing through a lot of unknown (decompiled) code untill I finally hit on a caught exception. It turned out that a newly added advice would convert the result to a JSON message for audit logging and this particular conversion would result in a StackOverflowException when the result contained binary data (through a javax.activation.DataHandler object).

So if you happen to have this error, you might also want to check out your aspects ;)


1 Comment

Simple Angular.JS 1.3 directive for Bootstrap tooltip

This is a very simple Angular.js (1.3) directive to add a Bootstrap 3 tooltip to any component.

angular.module('angularBootstrapTooltipApp')
  .directive('angularTooltip', function () {
    return {
      restrict: 'A',
      replace: false,
      scope: {
        tooltipPlacement: '=?',
        tooltip: '='
      },
      compile: function compile( tElement, tAttributes ) {
        return function postLink( scope, element, attributes, controller ) {
          if (scope.tooltip !== '') {
            element.attr('data-toggle', 'tooltip');
            element.attr('data-placement', scope.tooltipPlacement || 'top');
            element.attr('title', scope.tooltip);
            element.tooltip();  
          }
          
          scope.$watch('tooltip', function(newVal) {
            if (!element.attr('data-toggle')) {
              element.attr('data-toggle', 'tooltip');
              element.attr('data-placement', scope.tooltipPlacement || 'top');
              element.attr('title', scope.tooltip);
              element.tooltip();  
            }
            element.attr('title', newVal);
            element.attr('data-original-title', newVal);
          });
        };
      }
    };
  });

A simple example:

<button type="button" 
    class="btn btn-default" 
    angular-tooltip
    tooltip-placement="bottom"
    tooltip="title">
{{title}}
</button>
<input type="text" ng-model="title"></input>

Whatever you type, the tooltip will always be up to date.
Screenshot from 2015-05-05 19:41:37

Comes with no guarantee, but feel free to use it wherever you need it.


Leave a comment

J-ExifTool v0.0.9

Today I’ve released version 0.0.9 of J-ExifTool. This release doesn’t add new features, but it’s more reliable so I highly suggest the upgrade:

  • Maven support ! It’s not in a public repository (yet), but it’s one step closer to using J-ExifTool in a maven project ;)
  • Buffers are more reliable
  • Some tests are re-written

The new jar can be downloaded from BitBucket.

For the record only: v0.0.9 is commit a780161.


Leave a comment

J-ExifTool v0.0.8

Today I’ve released version 0.0.8 of J-ExifTool. This release adds new functionalities and provides some bug fixes:

  • #8: Delete all exif tags
  • #12: extract thumnail image
  • #15: it’s now possible to read exiftool path from environment variable
  • Improved reading out the command line buffer (no more lost/split/incorrect values, tests are more stable now)

Since release v0.0.5 you’ll need the Apache Commons Exec library in your classpath. I’ve added a new lib folder on BitBucket which includes this new library or you can download the new library from Apache.

The new jar can be downloaded from BitBucket.

For the record only: v0.0.8 is commit 2f4414f.


Leave a comment

My IBM DB2 Cheat Sheet

This is a set of DB2 snippets I often used at work. I’m glad to share them with you.

Generate GRANT statements for a new DB2 user with name MYUSER user on all tables of a schema named MYSCHEMA:

select 'GRANT SELECT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA'
UNION
select 'GRANT INSERT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA'
UNION
select 'GRANT UPDATE ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA' @

The following stored procedure will automatically generate and execute the grant statements

CREATE OR REPLACE PROCEDURE MYSCHEMA.GRANT_ME_SOME (IN SCHEMANAME VARCHAR(128), IN USRNME VARCHAR(128))
       DYNAMIC RESULT SETS 1
       MODIFIES SQL DATA
P1: BEGIN
		DECLARE q VARCHAR(1024);
		DECLARE AT_END SMALLINT DEFAULT 0 ;
		DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
		
		DECLARE CUR CURSOR WITH HOLD FOR 
		select 'GRANT SELECT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME AS KWERIE from syscat.tables where tabschema = SCHEMANAME
		UNION
		select 'GRANT INSERT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME  AS KWERIE from syscat.tables where tabschema = SCHEMANAME
		UNION
		select 'GRANT UPDATE ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME  AS KWERIE from syscat.tables where tabschema = SCHEMANAME; 
	
		DECLARE CONTINUE HANDLER FOR NOT_FOUND SET AT_END = 1 ;
		OPEN CUR;
		
		UPDATE_LOOP : LOOP
		
		FETCH CUR INTO q;
		IF AT_END <> 0 THEN
	        LEAVE UPDATE_LOOP ;
	    END IF ;
		
		EXECUTE IMMEDIATE q;
		
		END LOOP;
END P1;

Generate ‘reorg index’ and and ‘reorg columns’ statements for all tables of a schema MYSCHEMA

select 'CALL SYSPROC.ADMIN_CMD(''RUNSTATS ON TABLE ' ||TRIM(tabschema)||'.'||TRIM(tabname)||' FOR DETAILED INDEX ALL '')@' from syscat.tables where tabschema='MYSCHEMA' and type='T' 
UNION
select 'CALL SYSPROC.ADMIN_CMD(''RUNSTATS ON TABLE ' ||TRIM(tabschema)||'.'||TRIM(tabname)||' ON ALL COLUMNS'')@' from syscat.tables where tabschema='MYSCHEMA' and type='T' @

Generate reorg table statements for all tables of a schema MYSCHEMA

select 'CALL SYSPROC.ADMIN_CMD(''REORG TABLE '||TRIM(tabschema)||'.'||TRIM(tabname)||''') @' from syscat.tables where tabschema='MYSCHEMA' and type='T'

Find all inactive tables on your database

SELECT COALESCE(
 'SET INTEGRITY FOR  "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || 
  '"  IMMEDIATE CHECKED @' , '')
FROM

 TABLE
 ( SELECT TABSCHEMA, TABNAME
 FROM SYSCAT.TABLES
 WHERE (CONST_CHECKED LIKE '%N%'
 OR STATUS = 'C')
 UNION

 SELECT a.REFTABSCHEMA,a.REFTABNAME
 FROM SYSCAT.REFERENCES a,SYSCAT.TABLES b
 WHERE (a.TABSCHEMA, a.TABNAME) IN
 (SELECT TABSCHEMA, TABNAME
 FROM SYSCAT.TABLES
 WHERE (CONST_CHECKED LIKE '%N%'
 OR STATUS = 'C'))
 AND a.REFTABSCHEMA = b.TABSCHEMA
 AND a.REFTABNAME = b.TABNAME
 AND (b.CONST_CHECKED LIKE '%N%'
 OR b.STATUS = 'C')

 ) AS TAB( TABSCHEMA, TABNAME )
ORDER BY TABSCHEMA
FETCH FIRST 50 ROWS ONLY
OPTIMIZE FOR 1 ROW

Quickly unload a table (sets your table in an invalid state). Works from CLI only

db2 LOAD FROM /dev/null of del REPLACE INTO MYSCHEMA.MYTABLE

An example of a DELETE WITH statement using the SELECT FROM OLD TABLE structure. I previously covered this statement.

WITH 
	DOCLINK(ID) AS ( SELECT ID FROM OLD TABLE (
			DELETE FROM MYSCHEMA.MYTABLE1 WHERE CODE = 'XYZ'
		)
	), 
	DOCU(ID) AS (SELECT ID FROM OLD TABLE (
			DELETE FROM MYSCHEMA.MYTABLE2 WHERE DOC_ID IN (SELECT ID FROM DOCLINK)
		)
	)	
	SELECT COUNT FROM MYSCHEMA.MYTABLE1 WHERE CODE = 'XYZ'
 @

An example of a MERGE statement. The field MYTABLE.JAAR will be updated with the value of the matching field in MYTABLE2.JAAR. It’s what you would do with UPDATE MYTABLE.JAAR = (SELECT JAAR FROM MYTABLE2 WHERE MYTABLE.VELD_ID = MYTABLE.ID) if it were a valid statement.

MERGE INTO MYSCHEMA.MYTABLE VELD 
	USING (
		select DISTINCT V.ID AS VELD_ID, T.JAAR AS JAAR 
		 from MYSCHEMA.MYTABLE2 T, MYSCHEMA.MYTABLE3 V
                 WHERE    V.ID = T.V_ID
			AND V.TYPE_ID IN (  100000,   100065,   100130)
			AND T.JAAR IS NOT NULL
	) AS VELD_TO_UPDATE
	ON VELD_TO_UPDATE.VELD_ID = VELD.ID
	WHEN MATCHED THEN UPDATE SET VELD.JAAR = VELD_TO_UPDATE.JAAR 
	@

A simple WITH statements

WITH IDS(FRST, SCND, CNT) AS (
	SELECT MIN(ID), MAX(ID), COUNT(ID) FROM MYSCHEMA.MYTABLE
		WHERE ID > 105220
		GROUP BY EXT_ID, INT_ID
		HAVING COUNT(ID) > 1
) SELECT ID, REF FROM MYSCHEMA.MYTABLE2 WHERE
	IN in (SELECT FRST FROM IDS) 
	OR ID IN (SELECT SCND FROM IDS)
	ORDER BY REF
	FOR READ ONLY @

List aggregation (LISTAGG)

SELECT LISTAGG(REF, ', ') WITHIN GROUP(ORDER BY ID) 
   FROM MYSCHEMA.MYTABLE GROUP BY REF_PREFIX

Drop a schema (CLI)

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('MYSCHEMA', NULL, 
 'DELETESCHEMA', 'ERR_DEL')"

Copy a schema (CLI)

db2 "CALL SYSPROC.ADMIN_COPY_SCHEMA('MYSCHEMA', 'MYNEWSCHEMA', 
 'COPY', null,  null, null, 'ERRORSCHEMA', 'ERRORTAB') "

DB2Advisor on schema MYSCHEMA (CLI) using an input file YOUR_QUERIES (you can also pass a password separately with -x)

db2advis -d MY_DB -t 30 -m IMP -noxml -i YOUR_QUERIES.SQL 
 -a USERNAME/PASSWORD -o OUTPUT.OUT -n MYSCHEMA -q MYSCHEMA

Create explain tables in your scherma.

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), 
 CAST ('MYUSER' AS VARCHAR(128)))
Follow

Get every new post delivered to your Inbox.

Join 47 other followers