DB2 SQL Error: SQLCODE=-1585, SQLSTATE=54048, SQLERRMC=null

Solving the subject error can be very easy or might be a bit tricky. If you know the exact query that causes this query, you have came a long way in solving it. If not, you will have to follow the steps we discuss later in this post.

The first thing I do when I have an error in DB2 is asking DB2 for more details about that error using db2 ? <> (i.e. db2 ? SQL1585N). DB2 tells "SQL1585N  A temporary table could not be created because there is no available system temporary table space that has a compatible page size."

You can be lucky and the query requires a system temp table space with page size 32K or less. Most of the times, a system temp table space is needed during the sort or joins. Creating the required table space will make your life much easier. You can check the available table spaces and their sizes using db2pd command. db2pd -d db_name -tablespace

Sometimes, you are not very lucky but you know the query. In that case, you may need to change the query. You may not be lucky at all -like me- and don't know the query causing this problem. First, we need to identify the query. Either using a snapshot or may be event monitor. Once you have got the query, get the access plan and detect the node causing the creation of temp table and try to optimize. Possible optimization may be dropping some columns from the selection list, truncate some character fields to shorter length ... etc

Finally, you will be able to make the query run successfully.
Good luck!