for testing only: try to run it only with the help option.
What do you get then ?
Also: is the ';' needed or better a plain ; or ";" (but this might very much depend upon the shell being used)
Another way (my favorit, since it does not rely on packages and stuff):
change your statement to :
>db2 connect to sample
>db2 explain plan with snapshot for select * from employee
to see the plan, either use the Control Center GUI, or if you prefer ASCII (what I do) use
database is sample, leaving the timestamp empty returns you the last explain from the explain table.
Don't forget to give an output file, that is too much to just look at, usually also too many lines to scroll back.
Also dynexpln is finally using packages. Be aware that in special cases with default optimize level 5 packages generate a slightly different access path than dynamic SQL. I have seen that in Version 7 EEE last time, still it can give you a headache if not aware.
Of course I expect having the explain tables created and the necessary authority (DBADM, SYSADM or see the doc for details)
whenever you want to explain (does not matter using which tool or utility) you need explain tables.
Since this is only little data this should not be a problem.
Either generate them using the Control Center GUI, or manually: in SQLLIB\misc is a file EXPLAIN.DDL which has the ddl and should be executed with the userid used later for explain as well (default schema = userid).
I thought only db2exfmt needs Explain tables and db2expln will collect in memory, Iam wrong then.
The theory here is, user has to have the EXPLAIN tables in order to run explain utilities. In this case, he has to have a tablespace to store EXPLAIN tables. But, my user does not have tablespaces to use. So, first he need to have a tablespace to use/create EXPLAIN table.
Well, you being the administrator could do the explain for him if it is only a task done sometimes, and for you it should not be a problem.
If you have Development - Integration - Production instances: I usually create one tablespace for all developers to use for explain tables in Development and Integration; only if really needed in production - or I do explain there myself.
If that is too open for you: you can create the tables for them using the EXPLAIN.DDL and SET CURRENT SQLID = ... , followed by GRANT on the tables.
As it turns out: usually only few developers become explain specialists - in case of problems the others go to them anyway. so it is not that you get hundreds of new tables or so ...